Delete Duplicates without creating new table

[mlrucci]

Hello,
I have a excel spread sheet that I am importing. I would like the end user to check for duplicates and then delete the duplicates from a table without creating a new table. I am using the below code, works great, for one criteria. The problem..I would like to use it based on three criteria. I have tired, what feels like a million different manipulations, without success. Anyone have an idea?

Dim dbs         As DAO.Database
Dim strSQL      As String
Dim varLastVal  As Variant
Dim strTable    As String
Dim strField    As String
strTable = "tblImportExec"
strField = "NUM"
Set dbs = CurrentDb
'Recordset must be full table but sorted by the field we're checking
strSQL = "SELECT * FROM [" & strTable & "] ORDER BY [" & strField & "]"


    With CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
        varLastVal = Null
        'For each record, check against previous value in strField
        'If same then this is a duplicate so can be removed
        Do Until .EOF
            If .Fields(strField) = varLastVal Then
                Call .Delete
            Else
                varLastVal = .Fields(strField)
            End If
            Call .MoveNext
        Loop
    'Ending the 'With' releases the Recordset
    End With

[kd2017]

So only records that have ALL 3 fields the same should be deleted?

Here is an idea
1. get a list of all the rows that have duplicates maybe like this:

'each row returned by this query has duplicates
sql = "SELECT fld1, fld2, fld3 FROM tbl GROUP BY fld1, fld2, fld3 HAVING Sum(1) > 1"

2. iterating through the previous list, query another recordset WHERE the fields = values from previous query, skip first record, delete remaining records...?

I'm sort of 'thinking out loud' here

[ssanfu]

It would have been nice to have more details: the names of the other fields, but try this.
All 3 criteria MUST match to delete the record. If only 0, 1 or 2 match then the record IS NOT deleted

        'Dim dbs         As DAO.Database      '<<-- not used
        Dim strSQL      As String
        Dim strTable    As String
        
        Dim strField1    As String
        Dim strField2    As String
        Dim strField3    As String

        Dim varLastVal1  As Variant
        Dim varLastVal2  As Variant
        Dim varLastVal3  As Variant


        strTable = "tblImportExec"
        
        strField1 = "NUM"
        strField2 = "2ndCriteria"    '<-- change to your field name
        strField3 = "3rdCriteria"    '<-- change to your field name

        'Set dbs = CurrentDb     '<<-- not used

        'Recordset must be full table but sorted by the fields we're checking
        strSQL = "SELECT * FROM [" & strTable & "] ORDER BY [" & strField1 & "], [" & strField2 & "], [" & strField3 & "]"
        Debug.Print strSQL

            With CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
                varLastVal1 = Null
                varLastVal2 = Null
                varLastVal3 = Null

                'For each record, check varLastVal1, varLastVal2, varLastVal3 against previous values in strField1, strField2, strField3
                'If same then this is a duplicate so can be removed
                Do Until .EOF
                    If .Fields(strField1) = varLastVal1 And .Fields(strField2) = varLastVal2 And .Fields(strField3) = varLastVal3 Then
                        Call .Delete
                    Else
                        varLastVal1 = .Fields(strField1)
                        varLastVal2 = .Fields(strField2)
                        varLastVal3 = .Fields(strField3)
                    End If
                    Call .MoveNext
                Loop
            'Ending the 'With' releases the Recordset
            End With

(**air code**)

[kd2017]

I see ssanfu has replied. You can safely ignore me In fact it would probably be for the best.

So only records that have ALL 3 fields the same should be deleted?

Here is an idea
1. get a list of all the rows that have duplicates maybe like this:

'each row returned by this query has duplicates
sql = "SELECT fld1, fld2, fld3 FROM tbl GROUP BY fld1, fld2, fld3 HAVING Sum(1) > 1"

2. iterating through the previous list, query another recordset WHERE the fields = values from previous query, skip first record, delete remaining records...?

I'm sort of 'thinking out loud' here

This is untested "stream of consciousness" coding here but this is what I was thinking

Public Sub example()
On Error GoTo ErrHandler
    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim qry As String
    
    Set db = CurrentDb
    
    qry = "SELECT fld1, fld2, fld3 FROM tbl GROUP BY fld1, fld2, fld3 HAVING Sum(1) > 1;"
    
    Set rs1 = db.OpenRecordset(qry)
    
    If Not (rs1.BOF And rs1.EOF) Then 'make sure we found records
        rs1.MoveFirst
        Do While Not rs1.EOF
            qry = "SELECT ID FROM tbl WHERE fld1=" & rs1!fld1 & " AND fld2=" & rs1!fld2 & " AND fld3=" & rs1!fld3 & " ;"
            
            Set rs2 = db.OpenRecordset(qry, dbOpenDynaset)
            
            If Not (rs2.BOF And rs2.EOF) Then 'make sure we found records
                rs2.MoveFirst
                rs2.MoveNext 'start at the second record
                Do While Not rs2.EOF
                    rs2.Delete
                    rs2.MoveNext
                Loop
            End If
            rs2.Close
    
            rs1.MoveNext
        Loop
    End If
    
    rs1.Close
    
ExitHandler:
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set db = Nothing
    Exit Sub
    
ErrHandler:
    MsgBox Err.Description, , "Error #" & Err.Number
    Resume ExitHandler
    
End Sub

[mlrucci]

ssnafu. It worked like a charm, I figured out what I was doing wrong and you added items I missed. Thank you very much!!! KD2017, I didn't ignore your work. Thank you for your reply. I had already made adjustments in my current code and is working perfectly. Thank you both again for you help.. I always try to do it on my own, sometimes, it just isn't enough. I learn every day!!!

[ssanfu]

Happy to help....


BTW,

ssnafu

???

It is ssanfu

[mlrucci]

oops, very sorry!!! much respect!

[Micron]

It is ssanfu

Situation Normal...

[ssanfu]

Well, yeah.... most of the time..