Simple Query to find the latest records with criteria - Stumped!

[Fatnecks]

Hey everyone, first time poster here. I'm sort of a brute-force hack type, but I can usually manage to get done what I want to get done. I'm trying to create a simple query in an asset tracking database. I've created a super simple new dbase with just a few tables and records to demonstrate the problem.

I'm ultimately trying to get to a report that will show the latest oil change for each vehicle. So I figured I'd create a simple query first, then dump that into a simple report.



Tables:
1 - Service Type (Oil Change, Wash, Wax, Brakes, etc)
2 - Vehicles - just like it seems
3 - Service entries (Date, which vehicle, which type of service, notes, etc)

So in the database, I create a simple query (see Query1). It does as I'd hope and shows only the latest oil change for each vehicle. But I'd also like to add the notes field to the report. If I add the notes field into the query, the query seems to ignore the MAX in the totals area and shows ALL the service entries (see Query2).

This doesn't seem to be that complicated, but I can't seem to make it work. Advice? Thanks!

[madpiet]

Dummy.zipThe trick with the "most recent oil change" query is to create the first query that returns just the (VehicleID, Date, ServiceType)

Oh... the lookup stuff. YUCK. (Sorry, I've been using Access since forever, so long before that "feature" was added).

You're right about the Notes column disappearing. The trick is to build the query that gets the latest oil change per vehicle and then save that. Then create a second query and then add the "latest oil change" query to the query grid and join on both VehicleID and ServiceDate. Then you can add the Notes column.

(See the example in the database... likely clearer!)

[Fatnecks]

This is great, thank you so much! I had a feeling that I would end up with a sub-query. I even made one but couldn't make it work. I knew I was close, but I've never had to join fields like that before. Makes sense now and I'll have to file this into the toolbox that sits on top of my neck. Thanks again for the help!

Hey everyone, first time poster here. I'm sort of a brute-force hack type, but I can usually manage to get done what I want to get done. I'm trying to create a simple query in an asset tracking database. I've created a super simple new dbase with just a few tables and records to demonstrate the problem.

I'm ultimately trying to get to a report that will show the latest oil change for each vehicle. So I figured I'd create a simple query first, then dump that into a simple report.

Tables:
1 - Service Type (Oil Change, Wash, Wax, Brakes, etc)
2 - Vehicles - just like it seems
3 - Service entries (Date, which vehicle, which type of service, notes, etc)

So in the database, I create a simple query (see Query1). It does as I'd hope and shows only the latest oil change for each vehicle. But I'd also like to add the notes field to the report. If I add the notes field into the query, the query seems to ignore the MAX in the totals area and shows ALL the service entries (see Query2).

This doesn't seem to be that complicated, but I can't seem to make it work. Advice? Thanks!

[madpiet]

This is great, thank you so much! I had a feeling that I would end up with a sub-query. I even made one but couldn't make it work. I knew I was close, but I've never had to join fields like that before. Makes sense now and I'll have to file this into the toolbox that sits on top of my neck. Thanks again for the help!

It's a not terribly obvious solution. Once you see it, it seems incredibly simple. The "nothing but the facts" database made it a whole lot easier.

[June7]

SvcType and SvcVehicle in ServiceEntry should be Number Long Integer type fields, not text.

I NEVER use lookup fields in tables.

Alternative query is a correlated subquery using TOP N. Unfortunately, this type of query can perform slowly with large dataset.

SELECT * FROM tbl_ServiceEntry WHERE ServiceID IN (SELECT TOP 1 ServiceID FROM tbl_ServiceEntry AS Dupe WHERE Dupe.SvcVehicle=tbl_ServiceEntry.SvcVehicle AND Dupe.SvcType="1" ORDER BY SvcDate DESC);

Note the quote marks around 1 because field is text, not number.

If you attempt to join these tables, it will fail because cannot join text to number.

[madpiet]

I agree with June... that's why I took the lookup stuff out. I hate them. Maybe because of the "what you see is definitely not what you get".

If you look at the tables, I changed a bunch of column types so the queries would work. (I may have left that bit of explanation out).