Deleting records based on duplicates in one field

[Blackburnjl]

Hello forum.

I have been out of Access for a few years and cannot recall how to delete duplicate records in a table based on one field. The find duplicates query does not delete.

Is there a query that I can build to delete duplicates without manually doing so?

For example, I have a table call 'donors' that has the following fields. Many donors will be creative and use both formal as well as short names. The phone number is the key field but I cannot set a primary key as the table is rebuilt routinely with a make-table query.

I'd like to save the first occurance of the phone number and delete following duplicate records.

Billing Phone Number	Name	Address 1
484-657-5555	Thomas Smith	123 Main St
484-657-5555	Tom Smith	123 Main St

Any and all help is appreciated.

Jim B

[ranman256]

make a column in the table called: MARK (len = 1 string)
make a query, qsDupeList, to show the data and the MARK field, sort the query on the dupe fld: "[your Dupe Fld]", item1...
paste the code into a module.
run the code below to mark the duplicates.
the code will the marked ones if theres a dupe.
then delete them with a query where [MARK] = 'D'
usage:
RemoveDuplicates "qsDupeList", "DupeFld","Mark"

put this code into a module

Public Sub RemoveDuplicates(ByVal pvQry, ByVal pvDupeFld, ByVal pvMarkFld)    
'pvQry = query name
'pvDupeFld   = field with duplicate values
'pvMarkFld    = field to change when duplicate is found
Dim vMsg
Dim db As Database
Dim rst   'As Recordset
Dim qdf As QueryDef
Dim vCurrDup, vPrevDup, vCurrFld, vAddr
DoCmd.Hourglass True
     
Set db = CurrentDb
Set qdf = db.QueryDefs(pvQry)
Set rst = qdf.OpenRecordset(dbOpenDynaset)
vPrevDup = "*&%"
With rst
    While Not .EOF
        vCurrDup = .Fields(pvDupeFld) & ""
        
        If vCurrDup <> "" Then
                '-----------------------
                'MARK THE DUPES...
                '-----------------------
              If vPrevDup = vCurrDup  Then         'found a dupe so mark it
                    .Edit
                    .Fields(pvMarkFld) = "D"
                    .Update
              End If
        End If
        vPrevDup = vCurrDup
         
       .MoveNext
    Wend
End With
DoCmd.Hourglass false
Set qdf = Nothing
Set rst = Nothing
Set db = Nothing
Exit Sub
ErrRemove:
MsgBox Err.Description, , mkCLASSNAME & "::RemoveDuplicates():" & Err
End Sub

[orange]

If you are certain that you won't have 2 donors with the same phone number, then
you can identify BillingPhoneNumber as a unique index(no duplicates) on your table design.
When you append new records to your donor table, no duplicate phone numbers will be saved.The database will prevent the second billingPhoneNumber entry from being saved.