How to query most recent by date

[sawilliams]

Sorry my Subject sounds so cryptic. What I need is probably very easy...

I have a table with CUST-ID, AMOUNT, DATE, REF_NO. I want to pull, for each Customer, the most recent item and report all 4 fields. I did:

SELECT CUST-ID, Max(DATE) GROUP BY CUST-ID

and do get the most recent date but I don't know how to get the associated AMOUNT and REF_NO.

Help would be greatly appreciated...

 

[dhookom]

If you add the GROUP BY query to a query of your details, you can join the [CUST-ID] and [Date]/[MaxOfDate] fields to limit your results to the most current record for each Customer.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[PHV]

SELECT A.CUST-ID, A.AMOUNT, A.DATE, A.REF_NO
FROM yourTable AS A INNER JOIN (
SELECT CUST-ID, Max([DATE]) AS MaxDATE FROM yourTable GROUP BY CUST-ID
) AS B ON A.CUST-ID = B.CUST-ID AND A.DATE = B.MaxDATE

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sawilliams]

duh...

Thanks Guys. So easy, but only if you know how to do it. Many, many thanks.

sawilliams