Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2009
    Posts
    2

    Question Unanswered: Getting the latest transaction

    HI there,
    I have a table as follow:

    File, StatusDate

    24-7 8/8/2006 8:29:01 AM
    27-7 7/25/2008 5:29:01 PM
    24-7 4/25/2009 10:29:01 AM
    30-10 10/8/2006 8:29:01 AM
    30-8 2/8/2006 3:29:01 AM
    30-9 9/8/2006 2:29:01 AM
    30-9 4/4/2009 1:15:25 AM


    How can I filter to get just the latest transaction one removing the duplicate ones to have this:

    File, StatusDate

    [NOT DISPLAYED]24-7 8/8/2006 8:29:01 AM
    [NOT DISPLAYED]27-7 7/25/2008 5:29:01 PM
    24-7 4/25/2009 10:29:01 AM
    30-10 10/8/2006 8:29:01 AM
    30-8 2/8/2006 3:29:01 AM
    [NOT DISPLAYED]30-9 9/8/2006 2:29:01 AM
    30-9 4/4/2009 1:15:25 AM


    So I get this:

    24-7 4/25/2009 10:29:01 AM
    30-10 10/8/2006 8:29:01 AM
    30-8 2/8/2006 3:29:01 AM
    30-9 4/4/2009 1:15:25 AM

    Thanks
    Rico

  2. #2
    Join Date
    May 2009
    Posts
    258
    Hello Rico,

    You can use the MAX function and GROUP BY to get this information:
    Code:
    SELECT File, MAX(StatusDate)
    FROM theTable
    GROUP BY File
    Regards,

    Ax

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    ▲ Something like that
    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

  4. #4
    Join Date
    Jul 2009
    Posts
    2

    Talking

    Thanks,

    BTW: Im using access 97 so I have to put the following cause I was using 4 columns:

    SELECT File, MAX(StatusDate), col02, col03
    FROM theTable
    GROUP BY File, col02, col03


    Rico

Posting Permissions

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