I have a funny situation.  I get a staff roster that has five elements that should create unique records.  The source database must have additional attributes that are not made available to me, as I get duplicate records (generally two duplicates per record, perhaps 9,000 out of 300,000 every month).  I want to create a combination of a query that identifies the duplicates, and then a vba script that updates a "Duplicate_EDIPI" field so that I can retain the records (to take back to the source) but strip them out from other functions/queries.  

SQL Statement:

SELECT TOP 2 *
FROM (SELECT EDIPI, TI_CD, DMHRSI_Emp_Number, Count(*) AS [Count]
FROM tbl_DMHRSI_240930
GROUP BY EDIPI, TI_CD, DMHRSI_Emp_Number
HAVING COUNT(*) >1
)  AS Duplicates
ORDER BY Duplicates.EDIPI DESC;

VBA:

Details

Private Sub Identify_Duplicate_EDIPIs_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim i As Integer
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("qry_Duplicate_EDIPI_Amended")
    
    i = 0
    While Not rs.EOF
        i = i + 1
        If i = 2 Then 'update the second duplicate record
        'Update code here
            rs!Duplicate_EDIPI = 1
            rs.Update
        End If
        rs.MoveNext
    Wend
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

Copy

This isn't working out and was hoping I could be put on the right path by this community!