How can I filter for the most recent date for each set of records.

[lizbee]

In Access 2002, I have a one-to many query that displays several records for
each id value (each record with a field for a numerical (dollar value) and
each with a date associated with the numerical entry). How can I filter to
show only the record with the most recent date for each set of records with
the same id values? I only want one record, the most recent one, to show for
each id. I have used filters for date ranges, but haven't found anything to
specify "most recent". Since Access can sort, it seems logical that at could
also filter similarily. I haven't ever created a macro or used visual basic,
and am wondering if this is something I need to know more about to achieve
this. Trying to learn these by reading the instructions alone seems
daunting. Do you have any suggestions on how to get better training in these
areas? Thanks!

 

[lizbee]

Okay, first let me apologize to all of you who have read my post and wondered
why I didn't read all the other similar posts first. I don't blame you for
not responding. This was my first post and before I wrote it I searched for
anything containing the words "most recent date", and I'm not sure why I
didn't get all the results that actually exist for this question (I was on
the search page, not on the discussion page, so I wasn't yet viewing all the
actual posts.

That said, let me say thank you to all the knowledgable people who have sent
helpful replies to others. I learned how to do what I needed by reading your
posts and would like especially to say thank you to LeAnne whose reply to Tom
Alway on 8/16/2004 helped me the most. For anyone reading this still looking
for answers, here is what I did:

I clicked "new query" and then under "view" clicked SQL View. In the blank
screen that opened I typed:

SELECT HouseholdID, Max(Query.DoDate) AS MostRecentDate
FROM Addressq
GROUP BY HouseholdID

For reference back to my question, HouseholdID was the id number, DoDate was
the corresponding date, and Addressq was the original query that I used.
After looking at the results of my new query, I decided I wanted more output
fields so I dragged in the "amount" and some other values I wanted to display
and was disheartened to see the number of records change and the "most recent
date" displaying all the records again, not just the most recent records.
Being new at this, I wasn't sure why this happened but was able to remedy it
by selecting "Last" instead of "Group By" in the row for Total for these new
fields.

If any of you experts out there know why, we newbies appreciate all the help
we can get! Thanks again.
- Lizbee