Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Latest dat from Duplicate records (Access 2003 SP2)

    I have a table of Computers and their Last Logon Date.

    The thing is I have multiple records.

    I learned to create a similar table so I can append these multiple records into it and only come up with one unique record for each Computer.

    I want to know how to control it to pick the most recent data:

    Example:
    ComputerName: ====> User====> LogDate
    WOPR1239 SmithAm 7/16/08
    WOPR1239 JonesAm 5/05/08
    WOPR1239 TatePa 6/11/08
    WOPR1445 HenryJa 6/18/08
    WOPR1445 MooreJa 7/11/08

    So I want to be able to append 2 records from this list:
    WOPR1239 SmithAm 7/16/08
    WOPR1445 MooreJa 7/11/08

    These two because they have the most recent dates.

    Thanks

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

    Re: Using Latest dat from Duplicate records (Access 2003 SP2)

    First create a query based on the table.
    Select Query | Totals or click the Totals button on the toolbar.
    Add the ComputerName field (with Group By as Total option) and the LogDate field (with Max as Total option). Do *not* add other fields.
    This query returns the most recent LogDate for each ComputerName.
    Save this query as for example qryMaxDate.

    Next, create a new query based on the table and on qryMaxDate.
    Join them on ComputerName and on LogDate vs MaxOfLogDate.
    Add all fields from the table to the query grid (or the *). Do *not* add fields from the query.
    Select Query | Append Query, and specify the new table as target.
    Access should automatically fill in the Append to line.
    Select Query | Run or click the Run button on the toolbar.

  3. #3
    2 Star Lounger
    Join Date
    Sep 2002
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Latest dat from Duplicate records (Acces

    So simple.

    Thanks.

    I actually just needed the first part. I gathered the Single most current records and did a make-Table query.

    Then I used that new table to update my MASTER list.

    Thanks Hans!

Posting Permissions

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