DMax expression

[Jim L.]

I am working on a vehicle maintenance database, and would like a report to
show each vehicle's most recent inspection date. I based the report on a
query, which has the following expression as a field;
Expr1: DMax("[Completion Date]","[Work Order]")
The problem is, it assigns the same "most recent inspection date" to all
vehicles, instead of extracting each vehicles inspection date.
How can I get the most recent date for each individual vehicle.
Another problem I ran into, is extracting just the inspections. In the
"Work Performed" field of the query, I have a criteria that says Like "Insp*"
(in case someone spells it wrong, or there was additional work performed).
When the query is run, some records are displayed where "Insp" is nowhere in
the "Work Performed" field. Why is this happening.
Thanks in advance for your help.

 

[KC-Mass]

Hi Jim,

You need an aggregate query ( select "Totals" under View). Group by
VehicleID and select "MAX" Completion date. That will give you the last
date each vehicle was worked on. Create a second query
with this query joined to the original table on "Completion Date" and get
whatever data from the original table for the last date something was done.

Regards

Kevin

 

[BruceM]

Within the context of DMax you need to specify a Where condition. Assuming
there is a VehicleID field:
DMax("[Completion Date]","[Work Order]","[VehicleID] = " & [VehicleID])

Having said that, you are probably better off using the Totals query as
suggested in another post, although I'm not sure joining on CompletionDate
is the best choice. Rather, I would expect the join to be on VehicleID.
The details depend on the structure of your database.