Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Finding Current Record in Query (2002)

    Hi,

    I have a list bound to a query that contains [ID], [Status Description], [Status Date] and [Name]. [ID] is not an autonumber and may be a duplicate. The list should only show the most current [Status Date] for an [ID], hiding all the others. For example, an [ID] could have a status of "Active", "Discharged", "Hold" etc. all with different Status Dates. I only want the most current status and status date to be visible.

    I'm not sure how to set up the query to filter for this.

    Thanks,
    Leesha

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Finding Current Record in Query (2002)

    The most current status date would be the latest. As Dates are just number, the latest date would also be the largest.

    So a grouping query which find the Max of Status Date when grouped by ID would show the most recent date for each ID.

    The problem is that grouping queries are never updateable, so this may not be the fianl answer. It depends what you want to do with the list.
    Regards
    John



  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding Current Record in Query (2002)

    You need two queries to do this:

    First a query to retrieve the most recent date for each ID:
    - Create a query with only the ID and Status Date fields.
    - Select View | Totals or click the Totals button on the toolbar.
    - Set the Total option for the Status Date field to Max (it remains Group By for ID)
    - Save this query as qryMaxDate.

    Next, a query to retrieve the other information:
    - Create a query based on the original table/query and on qryMaxDate.
    - Drag a line from ID to ID to join the two.
    - Also drag a line from Status Date to MaxOfStatusDate.
    - There should now be two lines joining them.
    - Add * from the original table/query to the query design grid.

  4. #4
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Finding Current Record in Query (2002)

    Thanks Hans, that worked to perfection. It does update as well which was something I needed it to do.

    Leesha

Posting Permissions

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