Using Latest dat from Duplicate records (Access 2003 SP2)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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!

Reply | Quote