Angie said:
The date field is actually named ACTION_EFF_DATE. This SQL gives me records
1, 2 and 3 instead of 1, 3 and 5. 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?
If no records have the same INT_ID and ACTION_EFF_DATE combination then
the following query should get you close:
qryWho:
SELECT INT_ID, ACTION_EFF_DATE, LAST_NM, FIRST_NM, OTHER2
FROM tblWho
GROUP BY INT_ID, ACTION_EFF_DATE, LAST_NM, FIRST_NM, OTHER2
HAVING LAST_NM<>(SELECT A.LAST_NM FROM tblWho AS A WHERE A.INT_ID =
tblWHO.INT_ID AND A.ACTION_EFF_DATE = (SELECT MAX(B.ACTION_EFF_DATE)
FROM tblWho AS B WHERE B.ACTION_EFF_DATE < tblWHO.ACTION_EFF_DATE AND
B.INT_ID = tblWho.INT_ID)) OR tblWho.ACTION_EFF_DATE=(SELECT
MIN(A.ACTION_EFF_DATE) FROM tblWho AS A WHERE A.INT_ID = tblWho.INT_ID)
ORDER BY INT_ID, ACTION_EFF_DATE;
This query grabs any records with the first date for a given INT_ID
along with ones where the previous record (using ACTION_EFF_DATE to
order) has a different LAST_NM. The second subquery seems necessary
because trying Last() on LAST_NM involves trying to impose an order on
the subquery using the ACTION_EFF_DATE. I was shooting for a solution
that uses GROUP BY so that all the changes for all INT_ID's can be shown
at once or limited to a single INT_ID so ordering the subquery was out.
I added the following record to tblWho along with your values:
WID ACTION_EFF_DATE LAST_NM FIRST_NM INT_ID OTHER2
8 20000303 JONES JO 12346 YYY
!qryWho:
INT_ID ACTION_EFF_DATE LAST_NM FIRST_NM OTHER2
12345 19990609 DOE JANE XXX
12345 20060322 SMITH JANE XXX
12345 20061102 DOE JANE XXX
12346 20000303 JONES JO YYY
Now approach it from the other side timewise.
Using:
qryWho2:
SELECT INT_ID, ACTION_EFF_DATE, LAST_NM, FIRST_NM, OTHER2
FROM tblWho
GROUP BY INT_ID, ACTION_EFF_DATE, LAST_NM, FIRST_NM, OTHER2
HAVING LAST_NM<>(SELECT A.LAST_NM FROM tblWho AS A WHERE A.INT_ID =
tblWHO.INT_ID AND A.ACTION_EFF_DATE = (SELECT MIN(B.ACTION_EFF_DATE)
FROM tblWho AS B WHERE B.ACTION_EFF_DATE > tblWHO.ACTION_EFF_DATE AND
B.INT_ID = tblWho.INT_ID)) OR tblWho.ACTION_EFF_DATE=(SELECT
MAX(A.ACTION_EFF_DATE) FROM tblWho AS A WHERE A.INT_ID = tblWho.INT_ID)
ORDER BY INT_ID, ACTION_EFF_DATE DESC;
!qryWho2:
INT_ID ACTION_EFF_DATE LAST_NM FIRST_NM OTHER2
12345 20070301 DOE JANE XXX
12345 20060713 SMITH JANE XXX
12345 20050916 DOE JANE XXX
12346 20000303 JONES JO YYY
This seems to do what you want.
James A. Fortune
(e-mail address removed)