Find duplicate records

[tjnichols]

I would like to add a function to either my table or form to determine if the record being added is actually a duplicate. I would like it to ask the user if records these could possibly be duplicates (should list all possibilities) and then allow them to add the record if they decide it isn't. I think it should look at the name. Currently, my database has two separate fields for this - first and last name. Perhaps this idea could look at phone numbers and / or email addresses as well?

Attached is my database so you can look at what I have so far.

Thanks!

Tonya

 

[pr2-eugin]

Okay that is a HUGE database and is not simple straight forward. But based on your question, it is possible.. I have had the similar functionality deployed in my current DB..

    Dim tmpPC, tmpTel
    tmpTel = TEL.Value
    tmpPC = [POST CODE].Value
    
    If [POLICY STATUS].Value = "Active" Then
        If (Len(DLookup("[CUSTOMER ID]", "Customers", "(TEL='" &  tmpTel & "') And([POST CODE]='" & tmpPC & "')") & "")  > 0) Then
            tp = MsgBox("The details of the customer already exists in  the DB, CUSTOMER ID: " & DLookup("[CUSTOMER ID]",  "Customers", "(TEL='" & tmpTel & "') And([POST CODE]='" &  tmpPC & "')"), vbCritical)
        End If
    End If

I checked the Post Code and Telephone number. If there is a Match found then it pops up with a message box saying the records are already in there; providing the CustomerID. I have placed this code in the AfterUpdate property of a field where the user selects whether to activate.

Hope this helps.

 

[jdraw]

You have to decide what makes a duplicate a duplicate.

FirstName + LastName + PhoneNUmber + AddressCity + AddressZip

You could make a Unique compound index on the fields in your list.

You could check for a duplicate by using

If Dcount(myDuplicatesFieldsList) >0 Then
'duplicate
else
'no other record like this so not duplicate
End If

You might consider doing checks in the BeforeUpdate event so you could Cancel

Just a few thoughts to consider. Good luck.

 

[tjnichols]

This is what I came up with as it pertains to my database. The problem is that I SUCK AT WRITING CODE!!!! Yep - you guessed it - it doesn't work!

Can you help? Any help would be MOST APPRECIATED!

Private Sub LastName_BeforeUpdate(Cancel As Integer)
Dim tmpPC, tmpLastName
tmpLastName = LastName.Value
tmpPC = [MailingZIP/PostalCode].Value

If [LastName].Value = "Active" Then
If (Len(DLookup("[ID]", "Consumers", "(LastName='" & tmpLastName & "') And([MailingZIP/PostalCode]='" & tmpPC & "')") & "") > 0) Then
tp = MsgBox("The details of the customer already exists in the DB, ID: " & DLookup("[ID]", "Consumers", "(HomePhone='" & tmpTel & "') And([MailingZIP/PostalCode]='" & tmpPC & "')"), vbCritical)
End If
End If
End Sub

 

[pr2-eugin]

Well I gave you my coding, which I have written in my application. So you had to tailor it according to your needs, I had to make a check only for the records that had the field called [POLICY STATUS] whose value is Active. That is the reason I used a Check,

If [POLICY STATUS].Value="Active" Then
[COLOR=SeaGreen]'DLookUp code[/COLOR]
End If

So I do not think anyone will have their last name as "Active".. So remove that and your code should look something like...

If (Len(DLookup("[ID]", "Consumers", "([COLOR=Red]LastName[/COLOR]='" &  tmpLastName & "') And([COLOR=Red][MailingZIP/PostalCode][/COLOR]='" & tmpPC &  "')") & "") > 0) Then
    tp = MsgBox("The details of the customer already exists in   the DB, ID: " & DLookup("[ID]", "Consumers", "([COLOR=Red]HomePhone[/COLOR]='" & [COLOR=Yellow] [COLOR=SeaGreen]tmpTel[/COLOR] [/COLOR]& "') And([[COLOR=Red]MailingZIP/PostalCode[/COLOR]]='" & tmpPC & "')"),  vbCritical)
End If

Other thing I noticed was, you are checking on one one condition and displaying the result of another.. i.e. you are checking duplicates based on LastName and PostalCode and inside the If condition you are using Telephone and PostalCode..(Highlighted in red..) and also tmpTel is not declared in the Dim statement. (Highlighted in green)

Change all this small tweeks and you should be good to go..