I have a table that records a timestamp for each a record in another table is changed and what was changed. I'm trying to build a query that will list out the last thing changed for each record and when it was changed. Any help would be greatly appreciated. Below is a sample table layout.

ID Time Item
1 1/1/2005 1
2 1/2/2005 1
1 1/3/2005 2
2 1/4/2005 2
2 1/5/2005 1

Ned to get query to show
ID Time
1 1/3/2005 2
2 1/5/2005 1

Welcome to Utter Access Forums!

You will need to use a sub query or two queries for that:

First query will identify the latest date per each ID (let's name it qryMx)
Second query will return the additional data about the record with the latest date. The query will join the table with qryMx.

BTW, don't you have a PK for the table? It will make for an easier join for the second query.

If you need help with the queries syntax, we'll go through it.

HTH

I'm looking to achieve similar functionality; to identify all records with the lastest date and then run a sub query on those records, I'm very new to access so any help on the query syntax would be great.

Thanks

Doug,

Thanks for the quick reply. I do need some help on the queries.
How would I identify the latest date per each ID?

Thanks again,
Aaron

Hi Aaron and DangerMouse,

First, you will need to create a summary query (Group by/Totals):

Start a query wizard, add the table you are going to report on (we'll call it MyTable)
Add the following fields: ID, and Time and click on NEXT
Save that query as qryMx
In the design mode:
Click on the TOTALS icon (Sigma = Looks like an edged E)
Change the Group By under the Time field to Max
When you run this query it should return one record per ID with the latest date

Start another query in design view
Add MyTable and qryMx
Click on the ID in MyTable and drag to the ID in qryMx
A line will form between them
Now do the same from Time in MyTable to MaxOfTime in qryMx
Double click on the fields you want to report from MyTable... they will be placed in the query grid (you can also click and drag)

That should do it...

BTW, TIME is a reserved word and you should not use it as an object name.

You need a subquery to identify which records of the sub-group you are interested in. Something like:

Thanks - works greart