how do I find last record of a group using query/SQL in Acces

[Guest]

I have a database (access) of vehicles, where each vehicle can have many
transactions in any month. how do I access only the last record of a month
for eachj vehicle? I am seriously stuck on this one

 

[Allen Browne]

Use a subquery to identify the primary key for the most recent record for
that vehicle.

This example assumes the table name is tblService, and it has fields:
- ServiceID primary key
- VehicleID foreign key to a table of vehicles
- ServiceDate Date/Time, determines the most recent date.

SELECT ServiceID, Vehicle, ServiceDate
FROM tblService
WHERE ServiceID =
(SELECT TOP 1 Dupe.ServiceID
FROM tblService AS Dupe
WHERE Dupe.VehicleID = tblService.VehicleID
ORDER BY Dupe.ServiceDate DESC, ServiceID DESC);

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066