Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Look at previous record for comparison in SQL statements only

Discussion in 'Information Technology' started by hompie@hotmail.com, Jul 28, 2006.

  1. hompie@hotmail.com

    Expand Collapse


    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
    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.

    1;1;1/1/2005 0:00:00;1/5/2005 0:00:00;"BELGIUM";"BRUSSELS";
    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"
  2. Loading...

Share This Page