Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2006
    Posts
    28

    Unanswered: Query for getting 1 out of n "almost similar" entries

    I have a table like this:

    Code:
    
    CID   NAME   FEE   STATUS   DATE
    1     Jack   100   Paid     22/01/2007
    2     Alan   50    Voided   11/10/2008
    3     Sue    800   Unpaid   05/08/2007
    2     Alan   50    Paid     13/10/2008
    How can I create a query that returns all the records BUT only returns the one with the most recent DATE among the entries with the same CIDs? So the desired output would be the following:

    Code:
    
    CID   NAME   FEE   STATUS   DATE
    1     Jack   100   Paid     22/01/2007
    3     Sue    800   Unpaid   05/08/2007
    2     Alan   50    Paid     13/10/2008
    Thanks.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    There are a couple ways, here's one:

    SELECT *
    FROM someTable
    WHERE [date] = (SELECT MAX([date]) FROM someTable tableAlias WHERE tableAlias.id = someTable.id)

    Shortcut:

    SELECT *
    FROM someTable
    WHERE [id] = DMax("[date]", "someTable", "[id] = " & [id])
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jan 2006
    Posts
    28
    Sweet, thanks!

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    SELECT *
    FROM someTable
    WHERE [id] = DMax("[date]", "someTable", "[id] = " & [id])
    Erm... how does a max date resolve to be used with the ID field? Is this a mistake or some SQL trick I should know about?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That would be a mistake.

    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ok, thanks
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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