Delete Duplicates (leave 1)

[Tony2021]

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)
);

[#2 · Accepted solution]

@Tony2021 

Hi
I'm just about to go out but have a look at the 2 example apps in the attached zip file which show two different approaches to this

Hope one/both are of help

[#3]

@isladogs 

very nice.  works great!  I also like it that there are 2 fields to test on.   Thank you very much. 

[#4]

Glad it worked for you. Sounds like you used the simpler version based on a single query. Is that correct?

[#5]

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]));

[#6]

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]));

[#7]

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.

[#8]

@BrianWS1O 

To use the method you described, you should use a unique index for the fields that should not contain duplicates. Your suggestion of a composite primary key isn't necessary

[#9]

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

[#10]

Not true.

Setting a unique index works on a single field or multiple fields.

There is no need to set those fields as a primary key for this purpose.

In general, it is usually better to avoid the use of composite primary keys.

[#11]

I don't follow; how do you set an index in MS Access across multiple fields? For example, if I want to avoid duplicates and I have records with First Name, Last Name, City, and State, but I don't want that same person at that location entered twice, how do I set up an index to prevent that?

Also, you can create a composite PK just temporarily to append the records and weed out the dupes, then change back to using a different PK.

Can you explain why a composite primary key should be avoided? Is it a processing thing? Does it matter with record sets below a certain size? Does it appreciably slow down processing when adding records? I'd like to see documentation to determine when this is or isn't a concern.

[#12]

@BrianWS1O 

PMFJI:

You set a unique composite index like this in Access:

Of course, you could also include the multiple fields in a Composite Primary Key.

In my experience, the main reason NOT to use composite Primary Keys is the difficulty they present in multi-field queries. If you have to create joins on multiple fields, the SQL statement grows to an unwieldy and confusing state. I don't know if it impacts performance, though. 

[#13]

@BrianWS1O 

George has already shown you how to create a unique index for multiple fields.

I have used composite primary keys in the past but eventually stopped doing so (except in rare cases) because they tended to make managing queries & code more unwieldy. If you run an online search you will find articles both for & against their use. A fairly balanced (if rather complex) article can be found at https://medium.com/@pablodalloglio/7-reasons-not-to-use-composite-keys-1d5efd5ec2e5 though I've only skimmed the article

Lets take a simple reason for avoiding them. As well as not containing duplicates, primary keys cannot ever contain null values. So all those fields must ALWAYS contain data which at times may prevent a new record being saved. However, with a unique index, you can choose to ignore nulls.

Hope that helps

[#14]

That is a cool feature, I never knew Access would let you create a composite index like that! I'm putting that in my mental notepad for future use, it should save a lot of hassles. Thanks!

[#15]

Excellent. It is a very useful feature that you will probably use a lot now you know about it.