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

    Unanswered: Access 2010 - Fill In Date/Time for a given User List

    I have a simple table with the following fields:
    User Name: User
    Date: Date-Time1
    Time: Date-Time2

    I also have another table that translates Domain Usernames to Real Names
    User Name: UserName
    Real Name: RealName

    I have a query that pulls from the first table the latest time for each user listed, this is the code:

    SELECT [Username Translation].RealName AS UserName, Max([SecLog].[Date-Time1]+[SecLog].[Date-Time2]) AS LatestTime
    FROM [SecLog] INNER JOIN [Username Translation] ON [SecLog].User = [Username Translation].UserName
    GROUP BY [Username Translation].RealName
    ORDER BY [Username Translation].RealName;
    I need to now pull the entire list from the Username Translation table, and then fill in the latest login time, and if there isn't an entry in the SecLog table to return a blank in the LatestTime field.

    Is this possible or do I have to filter out missing people manualy?

    Thank you in advance.

  2. #2
    Join Date
    Dec 2004
    Madison, WI
    I'd highly recommend renaming the field Date and Time to something like DateLogIn and TimeLogIn since Date and Time are reserved words and can cause problems.

    Attached is an example that I hope will help. See qry2 and qry3. qry1 is used within qry2. qry3 is another way without a nested query.

    There's probably a few different ways this can be done. Hopefully this example will help.
    Attached Files Attached Files
    Last edited by pkstormy; 02-15-11 at 22:03.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Feb 2011
    I have renamed the fields to LoginDt and LoginTm

    The ZIP posted reads as corrupt in Windows and 7Zip brings out a no-extension file named the same as the zip.

    Any chance you could post the code in code boxes here?


Posting Permissions

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