Totals Query/Last Date

[Paul]

I have a table that has the following fields

K_ID (Primary Key)
C_ID (Foreign Key)
Date

I want to return the record with the most recent Date for
each C_ID and include the K_ID. I've tried using the
Totals query using both last and max but the query always
returns every record.

How can I return the record with the most recent date for
each C_ID?

Thanks for any help anyone can give!!

 

[Tom Ellison]

Dear Paul:

You can use the "classic" correlated subquery for this:

SELECT K_ID, C_ID, [Date]
FROM YourTable T
WHERE [Date] = (SELECT MAX([Date])
FROM YourTable T1
WHERE T1.C_ID = T.C_ID)

If two rows have the same C_ID and [Date] values then you may see more
than one row returned for some values of C_ID, since neither is the
only one that is most recent.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Dale Fye]

Another method, which I have found to be faster for large data sets is:

Select T.KID, S.C_ID, S.MaxDate
FROM yourTable T
INNER JOIN
(SELECT C_ID, MAX(Date) as MaxDate
FROM yourTable
GROUP BY C_ID) S
ON T.C_ID = S.C_ID
AND T.Date = S.MaxDate

BTW, Date is an Access key word and should not be used as a field name. Use
something more descriptive to describe what occured on the date (eg..
JobDate, TranDate, HireDate).

Dale Fye
Developing Solutions