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: 1
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: 71,744
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!

Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
post Mar 2 2018, 12:59 PM

UtterAccess Editor
Posts: 18,211
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,


(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    21st March 2018 - 01:50 AM