Latest record from table with repeatable entries and non repeatable entries

[corkbric]

Hi,

Have a database with multiple tables, one of the tables has several fields, but only 3 are of concern at the moment, ID, AgreementID and MaintenanceDate. ID is non repeating and link to other tables, AgreementID is repeatable and the MDate field just holds the date that maint was performed. Table keeps track of maint on customer equip, so ID 1 could have 50 entries, so forth and so on. Trying to find a way to pull the dates of the most recent maintenance times so those dates can be bounced with the current date to give a total number of days since last maint was performed. I just cannot figure it out. This is the last piece of the database to finish up. It's going to be a query that feeds a report. Any help would be greatly appreciated. Thanks.

[June7]

Can use an aggregate (GROUP BY) Totals query to return the latest date for each agreement.

SELECT AgreementID, Max(MaintenanceDate) AS LatestDate FROM tablename GROUP BY AgreementID;

If you want the entire record, here is one method http://allenbrowne.com/subquery-01.html#TopN

[corkbric]

Worked like a charm, tyvvm.