Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Last Date of Log On (XP)

    I have imported text from a log file which tracks user's activities for the entire year. Part of the text file contains tasks, dates and times associated to each user.

    What I would like to do is determine for each user the last time they ran a specific task. In a query, I created functions for the User, dates, time and task. My challenge is how to extract the latest date/time of a specific task by User. I believe I need another query but am unsure of the criteria formula.

    Your advice is appreciated.

    Thanks,
    John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Last Date of Log On (XP)

    Create a query based on the imported table.
    Add the user and task fields to the query grid.
    Create a calculated column:

    DateTime: [DateField]+[TimeField]

    where Datefield and TimeField are the names of the date and time fields.
    Select View | Totals.
    Set the Total option for the DateTime column to Max (the default Group By is OK for the first two columns).
    Switch to datasheet view.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Last Date of Log On (XP)

    Hans,

    I am still having a challenge. I created a new query named "Query2" which takes the UserName and DateTime fields from Query1. As instructed I Selected View>Totals. and then set the Total option for the DateTime column to Max. Upon switching to datasheet view, I encounter an error message: Invalid procedure call.

    Regards,
    John

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Last Date of Log On (XP)

    If you turn off View | Totals and switch to datasheet view, what do you see in the DateTime column?

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Last Date of Log On (XP)

    Hans,

    Turning off the View>Total produces a list of User's and their respetive Date/Times. It would appear that the Max setting in the Total is the challenge.

    Regards,
    John

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Last Date of Log On (XP)

    Try the following:
    - Save the query with the Totals option turned off.
    - Create a new query based on the saved query, and select View | Totals in the new query, with Max for the DateTime field.

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Last Date of Log On (XP)

    Hans,

    I tried your suggestion of creating a new query. I receive and error message: Invalid Proceedure Call.

    I tried a little experiment on the "Time" value only and tried to put a Total>Max within a new query and received the same error message. Is it possible that the time format is incorrect ie should be converted to a numeric value? The attached Bitmap file depicts what the "Time" records look like.

    Regards,
    John
    Attached Images Attached Images

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Last Date of Log On (XP)

    Access can calculate the maximum of a date/time value without problems.
    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Last Date of Log On (XP)

    Hans,

    I finally managed to get it to work.

    Thanks for your assistance,
    John

Posting Permissions

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