Results 1 to 2 of 2
  1. #1
    New Lounger
    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts