Look at previous record for comparison in SQL statements only

#1 (**permalink**) 28-Jul-2006, 08:21 AM

Hi,

I have a recordset with so many differences that distinct or distinct
row will now work.
I want to look at the previous record, and if it's the same except for
some fields, flag it in some way.

The table contains begin and and dates, an employee ID, and where he is
based in main location and sub location, if the main changed, i need to
find those and flag it as INTERNAT_MOVE.

As i'm using Acess pure as an intermediate between an old database and
a new system to convert analyse and flag data and then separate it, i
would prefer to have this is SQL statements, and not in VB, as that
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/11543-look-previous-record-comparison-sql-statements.html
would require a lot more steps.

Except for this single problem I've manaed to (thx to browsing this
group) perform al required translations in SQL, I've been playing with
thislast one but it's friday and i'm running out of time. Just a hint
on where to look for it would be nice. Note that the record nrs are not
really sequential, but the EMP ID is the same, but i can;t do recordID
-1 to search for the previous one

This is it:
Record ID 6 (EMP ID 1) needs to be flagged. Cause the previous record
for employee 1 , record ID 3 the main is different.

EMPID ID start end main sub INTERNAT_MOVE
1 1 01/01/2005 01/05/2005 BE BRUSSELS
2 2 02/01/2005 01/07/2005 BE BRUSSELS
1 3 02/05/2005 30/06/2005 BE ANTWERP
2 4 02/07/2005 31/08/2005 BE ANTWERP
3 5 01/01/2005 03/06/2006 FR LYON
1 6 01/07/2005 01/10/2005 FR PARIS true
2 7 01/09/2005 01/11/2005 BE GENT
1 8 02/10/2005 31/12/2005 FR LYON
1 9 01/01/2006 15/02/2006 SP MADRID

PS: this is fake data, my real table is a lot bigger, and linked, which
is why o made this simplified version just to test it out.

I've added some data which if copied pasted in a text file and imported
gives you some sample data. The first row is the header, separator is ;
, the second colum, ID, is the unique key, EMPID refers to a related
table with single row data of EMP ID.

"EMPID";"ID";"start";"end";"main";"sub";"TYPE"
1;1;1/1/2005 0:00:00;1/5/2005 0:00:00;"BELGIUM";"BRUSSELS";
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11543
2;2;2/1/2005 0:00:00;1/7/2005 0:00:00;"BELGIUM";"BRUSSELS";
1;3;2/5/2005 0:00:00;30/6/2005 0:00:00;"BELGIUM";"ANTWERP";
2;4;2/7/2005 0:00:00;31/8/2005 0:00:00;"BELGIUM";"ANTWERP";
3;5;1/1/2005 0:00:00;3/6/2006 0:00:00;"FRANCE";"LYON";
1;6;1/7/2005 0:00:00;1/10/2005 0:00:00;"FRANCE";"PARIS";"INTERNAT_MOVE"
2;7;1/9/2005 0:00:00;1/11/2005 0:00:00;"BELGIUM";"GENT";
1;8;2/10/2005 0:00:00;31/12/2005 0:00:00;"FRANCE";"LYON";
1;9;1/1/2006 0:00:00;15/2/2006 0:00:00;"SPAIN";"MADRID";"INTERNAT_MOVE"