deletion of duplicates records

[Guest]

In my database there are duplicate surnames, first_names, Initial,
the duplicates query can only get them out instead of the delete query to
delete just one of the duplicate records it will delete all the records

 

[Guest]

Need more info. What are all the field names in the table? Is there a
primary key on the table? Do you have nearly duplicate names such as not
having a middle initial, but still considered the same person? Does the table
contain a foreign key to any other tables>

 

[Guest]

Firstly add an autonumber column to the table if you don't have one already.
Lets call it ContactID. That will serve to uniquely identify each row. You
can then delete all but one row of each duplicated surname, first_name,
initial combination by using a subquery to identify all but those with the
lowest ContactID value. So, assuming the table is called Contacts the delete
query would go like this:

DELETE *
FROM Contacts AS C1
WHERE ContactID >
(SELECT MIN(ContactID)
FROM Contacts AS C2
WHERE C2.surname = C1.surname
AND C2.first_name = C1.first_name
AND C2,initial = C1.Initial);

Be sure to back up the table first!

Ken Sheridan
Stafford, England