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" <Sklyn@discussions.microsoft.com> wrote in message 
news:7364E148-8112-4866-8009-83BFD1D5924B@microsoft.com...
> 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/community/en-us/default.mspx?&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.access.forms&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" 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" <Sklyn@discussions.microsoft.com> wrote in message 
> news:7364E148-8112-4866-8009-83BFD1D5924B@microsoft.com...
> > 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" <Sklyn@discussions.microsoft.com> wrote in message 
news:48407266-49A2-4087-AC9A-D8D3E6CB9FFC@microsoft.com...
> 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/community/en-us/default.mspx?&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.access.forms&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" 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" <Sklyn@discussions.microsoft.com> wrote in message
>> news:7364E148-8112-4866-8009-83BFD1D5924B@microsoft.com...
>> > 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
>>
>> .
>>