Latest Date

[drewdt3]

I have a small fleet of vehicles that I need to keep a check on the last services. I want to adjust my query so that it only shows the last service for each vehicle, i.e. the latest date. I have messed around with the MAX function on the drop down box to no avail, can anyone please help a desperate man???

My fields in this query are: -

Reg
Date
next due date

 

[Jon K]

You can first build a totals query, Group By Reg, and Max Date. Then in a second query, link the first query back to the table to retrieve the records.

qryOne:-
SELECT Reg, Max(Date) AS MaxOfDate
FROM [TableName]
GROUP BY Reg;

qryTwo:-
SELECT [TableName].*
FROM [TableName] INNER JOIN qryOne ON ([TableName].[Reg]=[qryOne].[Reg]) AND ([TableName].[Date]=[qryOne].[MaxOfDate]);


Run the second query.
.

 

[drewdt3]

Thanks very much mate.

sorted!!