Forum Discussion
Delete Duplicates (leave 1)
Hello, I have duplicates in my table and I want to delete only 1 of them. I have searched on the net for some code and found the below and fashioned it to my case. I have an error though and it appears to be syntax. Do you see where I the error is? Also, if there is better code I am grateful for assistance.
Note: I am a novice user of access and not a programmer.
Delete duplicate records in ACCESS 2010. | Toolbox Tech
DELETE *
FROM [Import_Excel_wRepeats]
WHERE [ID1] IN
(SELECT [ID1] FROM
(SELECT [ID1]
FROM [Import_Excel_wRepeats]
WHERE [Section] In
(SELECT [Section] FROM [Import_Excel_wRepeats] As Tmp
GROUP BY [Section], [short description]
HAVING Count(*) >1
And [short description] = [Import_Excel_wRepeats].[short description])
WHERE ID1 NOT IN
(SELECT FIRST(ID1)
FROM [Import_Excel_wRepeats]
GROUP BY [Section], [short description]
HAVING Count(*) >1)
);
- this is from Allen Browne's site:
DELETE *
FROM [Import_Excel_wRepeats]
WHERE ID1 <> (SELECT Min(ID1) AS MinOfID FROM Table1 AS Dupe
WHERE (Dupe. [Section] = [Import_Excel_wRepeats].[Section]) AND (Dupe.[short description] = Table1.[short description]));- forgot to change Table1 there:
DELETE *
FROM [Import_Excel_wRepeats]
WHERE ID1 <> (SELECT Min(ID1) AS MinOfID FROM [Import_Excel_wRepeats] AS Dupe
WHERE (Dupe. [Section] = [Import_Excel_wRepeats].[Section]) AND (Dupe.[short description] = [Import_Excel_wRepeats].[short description]));
- Let's all write to Microsoft and ask them to add a "DELETE DUPLICATES" query wizard in the next release. We've all had to come up with hacky workarounds to do this at some point or other and it shouldn't be such a chore!
The other way I kluge it together is to make a new empty copy of the data table, select the fields (columns) that should constitute a unique record and make them into a Primary Key and then build a query to insert the records from the table with the dupes into it. When Access squawks about not being able to append all the dupes just tell it to go ahead anyway. Then you wind up with a table with no dupes.True, either way works...if you're only using one field to identify duplicates. If you have to combine several fields to determine a unique record then as far as I know you'd need to create a multi-field PK
