Self Join? (1 Viewer)

[rockies1]

I have data that looks like this:

Acct   Status   Date
1      A        1/1/2001
1      I        1/2/2002
1      A        2/3/2002
2      A        4/1/2002
2      I        4/5/2002
3      A        1/1/2002

I need a query that will return the current status, meaning the status with the most recent Date.

How can I get:

1      A        2/3/2002
2      I        4/5/2002
3      A        1/1/2002

Thanks!

 

[RV]

SELECT Acct, Status, AcctDate
FROM YourTable
WHERE AcctDate=
(SELECT Max(AcctDate)
FROM YourTable AS YourTable1
WHERE YourTable.Acct = YourTable.Acct);

P.S. Don't use Date as a column name as it's a reserved word in Access and can cause unwanted results.

RV

 

[rockies1]

Perfect!!

Thank you do much!

(I know about Date, it was just to keep the example simple...)