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?
SteveL13Asked:
Who is Participating?
 
PatHartmanCommented:
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.
0
 
Dale FyeCommented:
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.
0
 
hnasrCommented:
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
    
    rs.MoveFirst
    sf1 = rs("f1")
    sf2 = rs("f2")
    rs.MoveNext
    Do While Not rs.EOF
        Debug.Print rs("f1"), rs("f2")
        If rs("f1") = sf1 And rs("f2") = sf2 Then
            rs.Delete
        Else
            sf1 = rs("f1")
            sf2 = rs("f2")

        End If
            rs.MoveNext
    Loop
End Sub

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
SteveL13Author Commented:
Do I need to replace any part of the supplied code with my real table name and field names?
0
 
hnasrCommented:
Yes.
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")
with
Set rs = CurrentDb.OpenRecordset("SELECT * FROM yourTableName ORDER BY CustOrdN , DefectCause")
0
 
Dale FyeCommented:
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.
0
 
PatHartmanCommented:
Dale,
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".
0
 
Dale FyeCommented:
agreed.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.