UtterAccess.com
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    
 
   
Shoemaker7069
post Mar 2 2018, 11:13 AM
Post#1



Posts: 5
Joined: 2-March 18



Hello,

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
 
theDBguy
post Mar 2 2018, 11:16 AM
Post#2


Access Wiki and Forums Moderator
Posts: 73,276
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Welcome to UtterAccess!
welcome2UA.gif

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
 
doctor9
post Mar 2 2018, 12:59 PM
Post#3


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


Shoemaker7069,

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

CODE
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,

Dennis
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    26th September 2018 - 03:45 AM