Creating a table of recent values

[jgarnick]

I need to pull the most recent data from a table based on one field. There can be many records for the same id, but each record has a date type field. I need to pull the most recent record out of this table per id.

How can I do this? Thanks!

jgarnick
jgarnick@aol.com

 

[dneufarth]

Create a query for your table showing fields 'yourfieldID' and 'yourfieldDATE'.

Click on the 'Sum" toolbar button.

Under the 'yourfieldID' column set Total: to 'group by'

Under the 'yourfieldDATE' column set Total: to 'max'

run it.

Dave

 

[jgarnick]

Thanks Dave, the query works fine if I only include those two fields (id and date), but if I add another field that has the actual data I need, then it doesn't work. The problem is I then need to use this query along with two other tables to get my data source for a report!

Any ideas?



jgarnick
jgarnick@aol.com

 

[zach028]

I had a similar problem and solved it thusly...

Make a "structure only" copy of your original table. Set ID and Date as primary keys in the new table. Make an Update query from the original table. Have it sort so that the fields you want are first (by ID then descending by date). This will produce a table with the most recent date for each ID with one record per ID.

Not elegant, but it should work.

Zach

 

[dneufarth]

Try converting 'max' query into a 'make-table' query.

Then create a second query that joins the newly created table and the original table on the ID/ID and DATE/MaxOfDATE fields to include the fields you want as well as any other tables you need to join to get all fields you need.

Automate by calling them through a sub routine.

Seems ripe for use of subquery, but haven't explored that.

Dave