Results 1 to 2 of 2
Thread: Scale down Access Query (2003)
2007-07-30, 14:35 #1
- Join Date
- Jul 2006
- Thanked 0 Times in 0 Posts
Scale down Access Query (2003)
I have a query that has generated over 5 million lines. I want to break it down further to just include the latest instance of a particular field.
Ex. User ID and Date posted. The user id may have several lines of date posted. I just want to query on the most recent date.
How do I run my query to only extract the user id and the most recent instance of date posted?
2007-07-30, 14:44 #2
- Join Date
- Mar 2002
- Thanked 30 Times in 30 Posts
Re: Scale down Access Query (2003)
Start by creating a query with just the UserID and DatePosted fields.
Select View | Totals to turn it into a totals query.
Set the Total option for the DatePosted field to Max (for UserID, it remains the default Group By).
This query returns the most recent date for each UserID.
Save and close the query.
Create a new query based on the original table or query and the one you just saved.
Join them on the UserID and DatePosted (vs MaxOfDatePosted) fields.
Add all fields from the original table or query (or the asterisk *).
This new query will return all fields, but just for the most recent date for each UserID.