Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2004
    Posts
    41

    Question Unanswered: Max Record by Date

    Should be an easy one, but I can't figure it out ... in a query some people have duplicate records, but i want the most recent record by the field 'Date Logged In'. I'm not sure of the criteria, or if i should put the max statement under the 'Date logged in' field itself or under the primary key field (SSAN)?
    Much thanks!

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    SELECT *
    FROM yourTable
    WHERE yourDateField = (SELECT MAX(yourDateField) FROM yourTable t1 WHERE t1.yourKey = yourTable.yourKey)
    oh yeah... documentation... I have heard of that.

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

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Max

    I just made this quick Min and Max query type example for another post. Maybe it will help and you can use it in some way to work for you.
    Attached Files Attached Files
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Dec 2004
    Posts
    41
    Thanks for the help Teddy ... can anyone help cleaning up/merging this criteria? I'm not too experienced on the SQL side!

    Current:
    WHERE (((Format([DATE LOGGED IN],"mmm-yy"))=[Enter Date (MMM-YY)]))

    New criteria to add/merge with above criteria:
    WHERE [DATE LOGGED IN] = (SELECT MAX(DATE LOGGED IN) FROM [MAIN TABLE Query] WHERE [MAIN TABLE Query].SSAN = [MAIN TABLE Query].SSAN)


    Many thanks!!!

Posting Permissions

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