Last date

[don]

Hi,

I'm using Access 2003. I have a table listing assets and when they
have been serviced. I need to create a query that lists all assets
but only the last service date and not all dates. I've drawn a blank
on how to do this and would much appreciate your help.

Many thanks in anticipation

DonH

 

[Jeff Boyce]

Don

One approach would be to create a select query, sorted by [ServiceDate] (I
assume you have a field with that info), then change the properties to show
the TOP 1.

Another would be to use a Totals query, aggregating by Max([ServiceDate]).

These are both predicated on your definition of "last" being "most recent".

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John Spencer]

Add your table to the query
Add the AssetID field and the Service Date field
Select View: Totals from the menu
Change Group By to Max under the Service Date field.

If you want additional fields returned - create another query
==Add your table and the above query as sources
==Set up a join between the table and the query by dragging from Asset to
Asset field and Service Date field to the Max Date field
==Add the remaining fields you want to see

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[don]

Add your table to the query
Add the AssetID field and the Service Date field
Select View: Totals from the menu
Change Group By to Max under the Service Date field.

If you want additional fields returned - create another query
==Add your table and the above query as sources
==Set up a join between the table and the query by dragging from Asset to
Asset field and Service Date field to the Max Date field
==Add the remaining fields you want to see

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County







- Show quoted text -

Thank you for your replies. Followed your advice John and works just
fine.

DonH