last entry per record query..

[Sklyn]

Hi and thanks for your time.

My database is tracking the service histroy of vehicles.
The main table has VIN as the PK which links to another table containing
details about each time the vehicle is serviced.

I need to make a query to then generate a report of when each vehicle was
LAST serviced. So my query would need to search each VIN and then only show
the most recent dated entry.

The main table is named "_VehicleDetails" and the related table is named
"ht_ServiceHistory"

I am fairly new to access so please try to keep it simple.
If I need to use code then please advise where the code needs to go also..
I'm hoping it can be done simply in design view

Thanks again

 

[Allen Browne]

1. In query design view, depress the Total button on the toolbar (big sigma
icon.) Access adds a Total row the the query design grid.

2. In the Total row under VIN, accept Group By.

3. In the Total row under your date field, choose Max.

This gives one record per serviced vehicle, with the latest date beside each
one.

If you want other fields from that record as well, it gets a bit more
complex. This might help:
http://www.mvps.org/access/queries/qry0020.htm

 

[Sklyn]

Thanks so much Allen, I was hoping you would come to my aid
I had a look through your site as I always do but couldn't find what I was
after.
I used the Cascading Queries solution from the link you gave.

Any chance you can help me out with this problem too? Its to do with
highlighting/colouring a field when a different field meets criteria.
http://www.microsoft.com/office/com...&p=1&tid=234491ce-5edb-4190-8266-70e3b4cda6e4

Your help is much appreciated, People like you make learning a lot more
enjoyable.
Thanks very much.
James

 

[Allen Browne]

Hopefully you'll get a reply to your thread.

Without reading it, there are several approaches, e.g. Conditional
Formatting, Format property of text box, or events in the form/report.