CODEWITHSUNDEEP
ms-accessms-access-2010
Amiaki
Amiaki

Reputation: 61

Access Deleting Duplicates with query

I'm trying to make a query that will delete all the duplicate rows from an Access 2010 table. And my problem is that the query is deleting all the duplicate records (even original).

So let's say I have 1 record that is original and 2 records that are duplicates. Instead of deleting 2 duplicates, it deletes all 3.

Here is what I did:

DELETE *
    FROM Table
    WHERE [field1] IN (SELECT [field1] FROM [table] As Tmp 
    GROUP BY [field1],[field2],[field3],[field4],[field5] HAVING Count(*)>1);

And Primary Key field is (ID)

Upvotes: 3

Views: 8750

Answers (3)

Shrishail
Shrishail

Reputation: 11

We can use this query to delete duplicates and retain the unique one, Ex. 3 duplcates found and it retains 1 and deletes other 2.

Delete * From myTable Where UniqueID Not IN (select UniqueID From myTable Where UniqueID IN (select Min(UniqueID) From myTable Group by [Duplicates]))

Thank you.

Upvotes: 0

MillGis
MillGis

Reputation: 11

Following Code will generate queries which work same as @HansUp mentioned. This should give an advantage if you like to check a lot of different tables for duplicates. Besides deleting "all" you even have the chance to delete a selection of duplicates.

Would be great to give some ideas for improvement:

Public Sub CleanUpDuplicates(strTable As String, strQuery, strID As String, strField1 As String, _
                Optional strField2 As String, _
                Optional strField3 As String, _
                Optional strField4 As String, _
                Optional strField5 As String)

    strQueryDUP = strQuery & "_DUP"
    strQueryCleanUp_Single = strQuery & "_CleanUp_Single"
    strQueryCleanUp_All = strQuery & "_CleanUp_All"

    'SELECT [field1], [field2], [field3], [field4], [field5], Min([ID]) AS keyID
    strSQLDUP = "SELECT [" & strField1
    If strField2 <> "" Then strSQLDUP = strSQLDUP + "], [" & strField2
    If strField3 <> "" Then strSQLDUP = strSQLDUP + "], [" & strField3
    If strField4 <> "" Then strSQLDUP = strSQLDUP + "], [" & strField4
    If strField5 <> "" Then strSQLDUP = strSQLDUP + "], [" & strField5
    strSQLDUP = strSQLDUP & "], Min(" & strID & ") AS keyID "

    'FROM [Table]
    strSQLDUP = strSQLDUP & "FROM " & strTable & " "

    'GROUP BY [field1], [field2], [field3], [field4], [field5];
    strSQLDUP = strSQLDUP & "GROUP BY [" & strField1
    If strField2 <> "" Then strSQLDUP = strSQLDUP + "], [" & strField2
    If strField3 <> "" Then strSQLDUP = strSQLDUP + "], [" & strField3
    If strField4 <> "" Then strSQLDUP = strSQLDUP + "], [" & strField4
    If strField5 <> "" Then strSQLDUP = strSQLDUP + "], [" & strField5
    strSQLDUP = strSQLDUP & "];"


    'FROM [Table]
    strSQLCleanUp = "FROM " & strTable & " "

    'WHERE [Table].ID Not In (SELECT keep_id FROM [Query]);
    strSQLCleanUp = strSQLCleanUp & "WHERE " & strTable & "." & strID & " Not In (SELECT keyID FROM "
    strSQLCleanUp = strSQLCleanUp & strQueryDUP & ");"

    'SELECT [Table].*
    strSQLCleanUp_Single = "SELECT " & strTable & ".* " & strSQLCleanUp
    'DELETE [Table].*
    strSQLCleanUp_All = "DELETE " & strTable & ".* " & strSQLCleanUp



    '###### Create Queries ##########
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim qdfDUP As DAO.QueryDef
    Dim qdfCleanUp_Single As DAO.QueryDef
    Dim qdfCleanUp_All As DAO.QueryDef

    On Error Resume Next
        DoCmd.DeleteObject acQuery, strQueryDUP
        Set qdfDUP = db.CreateQueryDef(strQueryDUP, strSQLDUP)

        DoCmd.DeleteObject acQuery, strQueryCleanUp_Single
        Set qdfCleanUp_Single = db.CreateQueryDef(strQueryCleanUp_Single, strSQLCleanUp_Single)

        DoCmd.DeleteObject acQuery, strQueryCleanUp_All
        Set qdfCleanUp_All = db.CreateQueryDef(strQueryCleanUp_All, strSQLCleanUp_All)
    On Error GoTo 0

    DoCmd.OpenQuery strQueryCleanUp_Single, acViewNormal

    response = MsgBox("Do you like to clean-up all duplicates?", vbOKCancel + vbCritical, "Attention")
    If response = vbOK Then
            DoCmd.Close acQuery, strQueryCleanUp_Single
            DoCmd.OpenQuery strQueryCleanUp_All, acViewNormal
        End If
End Sub

Upvotes: 1

HansUp
HansUp

Reputation: 97101

Access will never allow you to DELETE in a GROUP BY query. So you need a fundamentally different approach.

Create a query which returns only one row for each of the duplicated field groups:

SELECT [field1], [field2], [field3], [field4], [field5], Min([ID]) AS keep_id
FROM [table]
GROUP BY [field1], [field2], [field3], [field4], [field5];

Save that as qryKeepIDs, and then use it a DELETE query:

DELETE FROM [Table]
WHERE [Table].ID Not In (SELECT keep_id FROM qryKeepIDs);

Upvotes: 3

Related Questions