How design query to delete duplicate records in a table

I have a table with the following fields:

CustOrdN and DefectCause.  There are other fields also but wherever the CustOrdN and DefectCause are duplicated, I want to delete all but one record.

How can I do this?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
Well, the problem with this is what is in the other records?  How do you determine which record to keep and which to delete within the duplicates?  If you have a Date field, or an autonumber ID field, you could elect to use a rule that lets you delete all but the first or earliest, or last/most recent date.  Generally, I just run a Duplicates query using the query wizard, where I identify the fields I want to use to define the duplicates.  Then I select to display all of the other fields as well, so that I can examine each record to determine which record to keep and which to delete.  This also gives me the ability to create a composite record from the data in multiple records, before deleting, if I need to.
The simplest way to get rid of duplicates is to recreate the table.
1. Rename the existing table by adding "_OLD" as a suffix
2. Copy the table - structure only and give it the original name.
3. Open the empty table in design view and set the primary key to be whatever column (or columns - up to 10) that uniquely defines a record.
4. Create an append query that  sorts by some of the other non-unique columns if you want to control which row gets added first.  
5. Run the append query.  You'll get an error message telling you that some rows were not appended but that is what you want.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Hamed NasrRetired IT ProfessionalCommented:
Try this way in a command button click event:
This example assumes table: b(f1, f2).
It loops through the table's record set and comparing current record with previous one, and deletes if it is a duplicate.

Private Sub Command4_Click()
    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM b ORDER BY f1, f2")
    Dim sf1 As Variant, sf2 As Variant ' hold the previous record fields values to compare with
    sf1 = rs("f1")
    sf2 = rs("f2")
    Do While Not rs.EOF
        Debug.Print rs("f1"), rs("f2")
        If rs("f1") = sf1 And rs("f2") = sf2 Then
            sf1 = rs("f1")
            sf2 = rs("f2")

        End If
End Sub

Open in new window

Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

SteveL13Author Commented:
Do I need to replace any part of the supplied code with my real table name and field names?
Hamed NasrRetired IT ProfessionalCommented:
Replace b with your tablename
Replace f1 with CustOrdN and and f2 with DefectCause in all lines of code.

Example: Replace
Set rs = CurrentDb.OpenRecordset("SELECT * FROM b ORDER BY f1, f2")
Set rs = CurrentDb.OpenRecordset("SELECT * FROM yourTableName ORDER BY CustOrdN , DefectCause")
Dale FyeOwner, Developing Solutions LLCCommented:
Just keep in mind that both of those two latter techniques are "dumb" techniques, which work great if you are not concerned with any of the data in the other fields of the table.

I don't mean they are "dumb" as in "I wouldn't use them", I just mean that you have no control over the data in any of the other fields, so you may keep the one record with nothing in any other field, while several of the other records (deleted) have values in those fields.
The method I suggested included a suggestion to use a sort to control which of the "duplicates" gets added so it is not totally "dumb".
Dale FyeOwner, Developing Solutions LLCCommented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.