Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2011
    Posts
    4

    Unanswered: Access 2010 - Query Latest Time for Each Unique User

    I have a simple database, three fields.

    UserName: Text
    Date: Date
    Time: Time

    Username contains multiple entries for the same user, Date contains multiple duplicates, Time contains unique entries within the scope of each user.

    I need to query so that I will end up with the latest time record for each user.

    If I simply do SELECT MAX it will only give me the latest time, not the latest time for each user.

    Is there a simple way to do this?

    Thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Try:
    Code:
    SELECT [Table].[UserName], Max([Table].[Date]+[Table].[Time]) AS LatestTime
    FROM [Table]
    GROUP BY [Table].[UserName]
    ORDER BY Max([Table].[Date]+[Table].[Time]);
    By the way, using reserved words (Date, Time) for naming columns in a table is not a good idea. One can even say that it's looking for trouble in your application, sooner or later.
    Have a nice day!

  3. #3
    Join Date
    Feb 2011
    Posts
    4
    Thanks, I'll give that a shot.

    The fields are actually named Date-Time1 and Date-Time2 but I simplified for the sake of clarity.

    EDIT:

    The solution worked perfectly, although for some reason it only exports the Date to Excel and strips the time, but I'm not so bothered about that, I only need the date.

    Thank you.
    Last edited by HeretiCorp; 02-15-11 at 13:09.

Posting Permissions

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