last entry per record query..

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

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Sklyn" wrote in message
...
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

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/comm...6-70e3b4cda6e4

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

"Allen Browne" wrote:

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Sklyn" wrote in message
...
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

.

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Sklyn" wrote in message
...
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/comm...6-70e3b4cda6e4

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

"Allen Browne" wrote:

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Sklyn" wrote in message
...
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

.