Delete Duplicartes Query (1 Viewer)

[USA_Chancer]

Hiya - I created this simple delete query to delete all duplicate phone numbers from a master "Do Not Call List'...However it deletes ALL versions of the number from the table, including the original. What do I have to change so that it leaves 1 version of the number in the table?

thanks in advance -


In (SELECT [HOMEPHONE] FROM [Table Name] As Tmp GROUP BY [HOMEPHONE] HAVING Count(*)>1 )

 

[Jon K]

I think you can use NOT IN with the help of another field in the table that can be compared with Max(), for example a [Date] field, a numeric field or even a text field:-

Delete *
FROM TableName as a
WHERE [Date] not in (Select Max([Date]) from TableName where HomePhone=a.HomePhone);


This will retain the most recent record for each [HomePhone].

Note. Running NOT IN may take time if the table is large as it cannot be optimised. Using the help of a date/numeric field can run faster than a text field.

Hope it helps.

 

[USA_Chancer]

Got It

Thx - from another forum - much simpler for anyone else who needs it...copy and paste the table structure to a new table, make the phonenumber field a primary field. use an append query to append the fields to the new table. delete the old table and rename the new. works great.

hagd....