Help removing duplicates which have oldest date.

[Kattt]

Hi guys.

I have a database of phone numbers and names and things. Each sheet is holding between 500k and 2 million records.

Each record has a date which it was called. Sometimes the same number is in the database up to 10 times, each with a different date that it was called.

What I need is to keep ONLY the latest record of the number being called.

So;
eg.

Number: 03 1234 1234 Called on: 13/06/08
Number: 03 1234 1234 Called on: 01/01/07
Number: 03 1234 1234 Called on 03/06/05

I want to delete all but the one that was called on 13/06/08.

If anyone can provide some insight, it would be greatly appreciated. Thanks - Kat.

[Stewart Ross]

Hi. One way to do what you want is to use a Make Table query to copy the latest records into a new table. That way you can either (a) retain the original table for reference purposes, or (b) delete it after making the new table.

The SQL for the make table query is along the lines of

SELECT [phonetable].[phonenumber], max([datelastcalled]) as LastCallDate
INTO [newtablename]
FROM [phonetable]
GROUP BY [phonetable].[phonenumber]

Why copy to a separate table? Because the SELECT above will only return one record per phone number per date, which a direct deletion cannot guarantee to do in the circumstances you have told us.

Given the information available it is at least not impossible in your original table that there may be more than one record for any one call date. In turn there could be more than one call record for the most recent call date. Directly deleting rows less than the most recent call date would not affect these duplicates, and dealing with them would entail a second pass. Copying to a new table provides a 'clean' copy of the most recently-dated records only and removes all duplicates in one operation.

-Stewart

[NeoPa]

I usually use an UPDATE / DELETE process when doing something like this. It does rely on having a spare field available as a simple Delete flag. It is also not the most efficient process when dealing with large recordsets, especially where large numbers of records need to be grouped together to determine which is the max or min etc. However, the restriction is generally only Access specific, as most SQL engines support deleting directly from a non-updatable query (as defined for Access).

The UPDATE (Flag) process is similar to the following. The fieldnames may well not match yours :

UPDATE [tblCalls] AS tC INNER JOIN [tblCalls] AS tC2
    ON tC.Number=tC2.Number
   AND tC.Called<tC2.Called
SET tC.SomeTextField='DeleteMe'

The DELETE process is then simply :

DELETE
FROM [tblCalls]
WHERE [SomeTextField]='DeleteMe'

Avoid using an indexed field as [SomeTextField] as this will impact severely on the performance.