How to delete duplicated records

[davidmcolaco]

Hi,

I have a table where I have a field called name. But there is a lot of equal names. How can I delete all the duplicated records and leave in the table different names.

Thanks in advance.

 

[ZmrAbdulla]

Create a "Find Duplacate" Query through the wizard then convert it to a delete query and run it.

BTW, word "Name" is reserved in Access. You need to change it to something like "FirstName" "FName" or something other than "Name". Access gets confused with reserved words by field names that bring trouble...

________________________________________________________
Zameer Abdulla
Help to find Missing people
There’s a world of difference between editorials and advertorials

 

[davidmcolaco]

I had some problems.
This is my code for the find duplicate, and when I converted it to delete, it deleted all the records.

SELECT First(Temp.mail) AS [mail Field], Count(Temp.mail) AS NumberOfDups
FROM Temp
GROUP BY Temp.mail
HAVING (((Count(Temp.mail))>1));

Can someone tell me what's wrong.

David

 

[PHV]

Say you have a unique field named ID:
DELETE FROM Temp
WHERE ID Not In (SELECT Max(ID) FROM Temp GROUP BY [mail]);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

There's nothing "wrong". Your query after converting it to a DELETE query would be something like

DELETE * FROM Temp

WHERE Temp.mail IN

(SELECT Temp.mail  
FROM Temp
GROUP BY Temp.mail
HAVING Count(Temp.mail))>1 )

which deletes ALL records that are duplicated. Use PHV's method to delete ALL BUT ONE of them.

 

[davidmcolaco]

Hi,

If I use PHV method it asks me for a ID.
It's suppose to do this?

David

 

[Golom]

PHV's suggestion was to select some unique field from your record and he used ID as an example. Select some real unique field that exists in your record in place of ID.

 

[ZmrAbdulla]

Say you have a unique field named ID:
DELETE FROM Temp
WHERE ID Not In (SELECT Max(ID) FROM Temp GROUP BY [mail]);

________________________________________________________
Zameer Abdulla
Help to find Missing people
There’s a world of difference between editorials and advertorials

 

[davidmcolaco]

Yes.. of course.. forget it..
Probably need to sleep more..

Thanks a lot..

David