Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jul 2006
    Posts
    3
    Thanks
    0
    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
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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
  •