select specific historical records

[bukky_bu]

Sample list of records:
DATE LAST_NM FIRST_NM INT_ID OTHER
1) 20070301 DOE JANE 12345 XXXXXXX
2) 20061102 DOE JANE 12345 XXXXXXX
3) 20060713 SMITH JANE 12345 XXXXXXX
4) 20060322 SMITH JANE 12345 XXXXXXX
5) 20050916 DOE JANE 12345 XXXXXXX
6) 20050427 DOE JANE 12345 XXXXXXX
7) 19990609 DOE JANE 12345 XXXXXXX

How do I pull records 1, 3 and 5? There are numerous other fields, but
none with any distinguishing characteristics. What I am trying to get
is an individual's name change history. This particular person changed
their name from Doe to Smith and back to Doe again. Everything I've
tried up to this point has
given me one instance each of Doe and Smith (1 and 3) when I really
need to return one instance of Smith and two instances of Doe (1, 3
and 5). Any advice?

 

[Michel Walsh]

For really large table or for small ones (1000-3000 rows)?

The strategy is the same but the tactic is different: You rank the records
by {id, date} (the numbers 1 to 7 you displayed) and you rank the records
by {id, lastName, date} :


1 1 20070301 DOE JANE 12345 XXXXXXX
2 2 20061102 DOE JANE 12345 XXXXXXX
3 6 20060713 SMITH JANE 12345 XXXXXXX
4 7 20060322 SMITH JANE 12345 XXXXXXX
5 3 20050916 DOE JANE 12345 XXXXXXX
6 4 20050427 DOE JANE 12345 XXXXXXX
7 5 19990609 DOE JANE 12345 XXXXXXX


Now, if you subtract both ranks, you get 3 groups (one with the difference
=0, one with difference =-3 and one with difference of +2) which means the
person got 3 names (one name per group). Take the MIN(date) of each group
to know when the database is aware of the 'changes'.


SELECT MIN(date), id
FROM somewhere
GROUP BY id, rank1-rank2



Now, the problem is how to get rank1 and rank2. If the table is really
large, I suggest to append the data in new (empty) tables, each with an
autonumber, but to use an ORDER BY clause in the INSERT INTO statement:



INSERT INTO tempTable1(id, date) SELECT id, date FROM myTable ORDER BY id,
date

and

INSERT INTO tempTable2(id, date) SELECT id, date FROM myTable ORDER BY id,
lastName, date


and then the autonumber fields in tempTable1 supplies rank1 and the
autonumber in tempTable2 supplies rank2. You can pump back these ranks and
associate them to the initial table, if required, with standard inner join
on {id, date}, or simply by:


SELECT MIN(a.date), a.id
FROM tempTable1 AS a INNER JOIN tempTable2 AS b
ON a.id=b.di AND a.date=b.date
GROUP BY a.id, a.autonumberField-b.autonumberField




Hoping it may help,
Vanderghast, Access MVP