find last historical record

G

Guest

Sample list of records:
DATE LAST_NM FIRST_NM INT_ID OTHER2 OTHER3 OTHER4
1) 20070301 DOE JANE 12345 XXX XXXX XX
2) 20061102 DOE JANE 12345 XXX XXXX XX
3) 20060713 SMITH JANE 12345 XXX XXXX XX
4) 20060322 SMITH JANE 12345 XXX XXXX XX
5) 20050916 DOE JANE 12345 XXX XXXX XX
6) 20050427 DOE JANE 12345 XXX XXXX XX
7) 19990609 DOE JANE 12345 XXX XXXX XX

How do I pull records 1, 3 and 5? There are numerous other fields, but none
with any distinguishing characteristics.
 
G

Guest

Hi, Angie,

first of all, DATE is not good identifier since Date is Access's reserved
keyword.

Here's SQL-string for your query
SELECT TOP 3 YourTable.*
FROM YourTable
ORDER BY YourTable.YourDate DESC;


HTH

Vlado
 
G

Guest

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?
 
J

James A. Fortune

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?
Click to expand...

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)
 
G

Guest

This makes sense to me, but when I try it, I get the error "At most one
record can be returned by this subquery". I fear I may have misinterpreted
something. Any suggestions?
 
J

James A. Fortune

Angie said:
This makes sense to me, but when I try it, I get the error "At most one
record can be returned by this subquery". I fear I may have misinterpreted
something. Any suggestions?

:
Click to expand...

Since that SQL worked on the data you supplied, I suspect that you have
more than one record with the same INT_ID and ACTION_EFF_DATE. I also
tried adding another record with the same INT_ID and ACTION_EFF_DATE and
got the error message you described. I "did" warn you about that case
:). Let me know what you want to do when that situation occurs and
I'll see if I can come up with something appropriate.

For example,

12345 19990609 DOE JANE XXX
12345 20060322 SMITH JANE XXX
12345 20060322 DOE JANE XXX
12345 20060322 SMITH JANE XXX
12345 20061102 DOE JANE XXX
12346 20000303 JONES JO YYY

What should be returned?

James A. Fortune
(e-mail address removed)
 
G

Guest

Ah!! You're right. You did mention that and I kind of blew over it thinking
I didn't have multiple records on the same date, but it turns out I do in
some cases. So, yes, I do need to throw that in the mix. Sorry about the
brain cloud!
 
J

James A. Fortune

Angie said:
Ah!! You're right. You did mention that and I kind of blew over it thinking
I didn't have multiple records on the same date, but it turns out I do in
some cases. So, yes, I do need to throw that in the mix. Sorry about the
brain cloud!
Click to expand...

Even the most intelligent people get brain freezes. Please re-read my
last post. Do you want to see all the name changes even if several
occur during the same day? Do you have a primary key in the table so
that ties in ACTION_EFF_DATE can be resolved? Would running a query to
obtain only the first entry for each day before running the SQL be enough?

James A. Fortune
(e-mail address removed)
 
G

Guest

I ran a quick query on my data. While there are instances of multiple
records for the same date, there are not multiple name changes occuring on
the same date. I do not have a primary key on my table, but I can add one.
My original query groups on a field called ACTION (and maxes on time stamp
because for some reason there are identical records on the same date for the
same action...go figure!). Sorry, I would have mentioned it before, but I
didn't think it would be this complicated. I've modified the original list
to reflect this and added some same-date records. Ultimately what I'd like
to see are historical name change fields populated with the name as follows.
I hope this makes more sense!

DATE LAST_NM FIRST_NM INT_ID ACTION HST_LST_NM HST_FST_NM
20070301 DOE JANE 12345 PAY RAISE DOE JANE
20070301 DOE JANE 12345 PROMOTION
20061102 DOE JANE 12345 ADDRESS CHG
20060713 SMITH JANE 12345 PAY RAISE SMITH JANE
20060322 SMITH JANE 12345 PROMOTION
20050916 DOE JANE 12345 TRANSFER DOE JANE
20050916 DOE JANE 12345 ADDRESS CHG
20050427 DOE JANE 12345 PAY RAISE
19990609 DOE JANE 12345 HIRE
 
J

James A. Fortune

Angie said:
I ran a quick query on my data. While there are instances of multiple
records for the same date, there are not multiple name changes occuring on
the same date. I do not have a primary key on my table, but I can add one.
My original query groups on a field called ACTION (and maxes on time stamp
because for some reason there are identical records on the same date for the
same action...go figure!). Sorry, I would have mentioned it before, but I
didn't think it would be this complicated. I've modified the original list
to reflect this and added some same-date records. Ultimately what I'd like
to see are historical name change fields populated with the name as follows.
I hope this makes more sense!

DATE LAST_NM FIRST_NM INT_ID ACTION HST_LST_NM HST_FST_NM
20070301 DOE JANE 12345 PAY RAISE DOE JANE
20070301 DOE JANE 12345 PROMOTION
20061102 DOE JANE 12345 ADDRESS CHG
20060713 SMITH JANE 12345 PAY RAISE SMITH JANE
20060322 SMITH JANE 12345 PROMOTION
20050916 DOE JANE 12345 TRANSFER DOE JANE
20050916 DOE JANE 12345 ADDRESS CHG
20050427 DOE JANE 12345 PAY RAISE
19990609 DOE JANE 12345 HIRE

:
Click to expand...

Since you don't have any name changes within a given date, perhaps it
makes sense to grab the first entry for each date:

qryWho1:
SELECT First(tblWho.ACTION_EFF_DATE) AS ACTION_EFF_DATE, tblWho.LAST_NM,
tblWho.FIRST_NM, tblWho.INT_ID, tblWho.OTHER2
FROM tblWho
GROUP BY tblWho.LAST_NM, tblWho.FIRST_NM, tblWho.INT_ID, tblWho.OTHER2
ORDER BY First(tblWho.ACTION_EFF_DATE);

qryWho2:
SELECT INT_ID, ACTION_EFF_DATE, LAST_NM, FIRST_NM, OTHER2
FROM qryWho1
GROUP BY INT_ID, ACTION_EFF_DATE, LAST_NM, FIRST_NM, OTHER2
HAVING LAST_NM<>(SELECT A.LAST_NM FROM qryWho1 AS A WHERE A.INT_ID =
qryWho1.INT_ID AND A.ACTION_EFF_DATE = (SELECT MIN(B.ACTION_EFF_DATE)
FROM qryWho1 AS B WHERE B.ACTION_EFF_DATE > qryWho1.ACTION_EFF_DATE AND
B.INT_ID = qryWho1.INT_ID)) OR qryWho1.ACTION_EFF_DATE=(SELECT
MAX(A.ACTION_EFF_DATE) FROM qryWho1 AS A WHERE A.INT_ID = qryWho1.INT_ID)
ORDER BY INT_ID, ACTION_EFF_DATE DESC;

Note: I only put OTHER2 in since the other OTHER's are similar.

James A. Fortune
(e-mail address removed)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

select specific historical records 1

Top