Is a Union Query the way to go?

[NISMOJim]

One more problem before the project is done (for now! Databases never seem to be done.).



tblWO contains vehicle work orders with fields like VehicleNumber, Odometer & CompletionDate, along with the work that was done & more vehicle details.
qryOil pulls all records from that table that have the OilChange checkbox checked.
qryInsp pulls all records from the table that have the Inspection checkbox checked.

What I want in the end is a report that shows the vehicle number, the date of the most recent oil change along with the odometer, and the most recent date of an inspection with the odometer all on the same line, and a seperate line (record) for each vehicle in the database.

I have made qryMaxOil & qryMaxInsp that show each vehicle number and the most recent date of each checkbox. I can only add VehicleNumber & CompletionDate to get the latest date. If I add the Odometer to the query then it shows all inspections instead of just the most recent.

From here, I'm not sure where to go. I was thinking a Union Query would bring both dates & odo's on the same record with the vehicle number. Instead, I get a prompt to enter the CompletionDate. When I click OK, it opens a list of some vehicle numbers (some are duplicated) and the completion date is blank.

There must be a better way to get the report I'm looking for. That's why I keep coming back to the experts! Thank you.

[June7]

So you need the latest Odometer reading for each vehicle for MaxOil and MaxInsp?

Would be helpful to have sample raw data as well as the SQL of queries, except for the UNION, I don't think it will be appropriate for your requirements.

[NISMOJim]

That is correct. I would like the report to look something like this

Vehicle Number Last Inspection Last Oil Change
A219 3/19/14 2145 2/24/14 1905
ATCTV09 4/1/14 12396 3/27/14 12285

I hope this & the attachment come out ok.