dmax in a query with date criteria

[Leo_Polla_Psemata]

Hi
I have a table with several fields, suppose it is about a fleet of vehicles which are moving from one point to another, may stray idle for some time until they get order for next activity.
There are too many fields which must be displayed in the result, i will outline only few important for the query.
one field is "vehicleid",
another field, "Actcode"
Another "Status"
Another "AvtivityDate"

The table may have 500 "vehicleid" but I get 3000 records because its "vehicleid" may have more than one "Actcode" and each "Actcode" one "AvtivityDate".

The latest "AvtivityDate" is the 'current', all the previous "AvtivityDate" is the history of "vehicleid"

How can i use the the dmax function in a query and retrieve only the 'current' records? Unfortunately there is no field "current", I have to make one in the query and then use it as criteria.

 

[plog]

I wouldn't use DMax, I would use a subquery to determine the latest date:

SELECT vehicleid, MAX(ActivityDate) AS CurrentActivity
FROM YourTableNameHere
GROUP BY vehicleid

Then use that to link back to YourTableNameHere to get all the fields of that one record.