X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Duplicates In Database Query With Bad Data., Access 2013    
post Mar 2 2018, 11:13 AM

Posts: 5
Joined: 2-March 18


So I have imported an Excel Spreadsheet into Access. The data is contact information. The source of the data from the Spreadsheet has all kinds of problems. I may have several blank fields or phone numbers entered different ways twice ..example 555-555-5555 and (555)555)5555. So I want to remove duplicates but Access sees something like that as a unique record and not a duplicate. So it might have a John Does Company and the Owner Name Be Bob and in five different fields under owner name Bob could be bob,bobb,blank,b ob or Bobb T. so access sees each ones unique> Anyway around this?

Thanks for any help.
Go to the top of the page
post Mar 2 2018, 11:16 AM

Access Wiki and Forums Moderator
Posts: 74,683
Joined: 19-June 07
From: SunnySandyEggo


Welcome to UtterAccess!

Access (computers) can only "estimate" likeness but the decision to declare them as duplicates would still fall on a human decision. So, it looks like you have a lot of work to do.

To help you along the way, take a look at SoundEx or the Levenshtein method. If you cannot find them, let us know, and we'll help you find some links.

Good luck!
Go to the top of the page
post Mar 2 2018, 12:59 PM

Posts: 18,324
Joined: 29-March 05
From: Wisconsin


You could create a custom function that only returns the digits from a string along these lines:

Public Function OnlyDigits(ByVal strInput As String) As String

    Dim i As Integer
    For i = 1 To Len(strInput)
        If IsNumeric(Mid(strInput, i, 1)) Then
            OnlyDigits = OnlyDigits & Mid(strInput, i, 1)
        End If
    Next i

End Function

Depending on how how many records you're working with, this could slow things down a bit, but it could help you find duplicates and normalize your data.

Hope this helps,

Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    23rd March 2019 - 07:41 PM