How to write a Similarity Query In Access Comparing Two Text Tables

[Galapagos15]

I have two tables which contain group ids and company names. These two tables were pulled from different sources so I need to determine whether or not the company names in each source were populated correctly. Table 1 could list group ID# 540 as ABC Company and Table 2 could list group ID# 540 as ABC Co. How can I write a query in Access which determines the percentage of similarity between the two company names. I have done this in Excel as a Fuzzy Lookup but need to find a way to do this in Access as well. Thanks in advance!

 

[Joe4]

Well, you could do an Unmatched Query between the two tables, and return all the records to do not have an exact match.

Otherwise, you many want to take a look at this, regarding Fuzzy Matches in Access: Microsoft Access tips: Fuzzy matches - Soundex

 

[Galapagos15]

Thank you for your assistance. I had tried Soundex but it won't show matches if the first name is switched such as Tom Smith and Smith Tom. I also tried the Levenshtein Distance Function but that doesn't work well for scenerios such as: William S Devine and W Steve Devine. Thanks again!

 

[Joe4]

Yes, there are so many variations and different situations, it may not be realistic to find a "fuzzy match" solution that is going satisfy all your needs.
Unfortunately, one can only do so much with "bad" or "inconsistent" data.

It is the programmers lament! I have dealt with situations like this before. Really, the best thing to do is a data clean-up to get all the data where it should be, and then have some standards going forward. But the clean-up can be a slow, tedious process. I feel your pain!

 

[Galapagos15]

Yes, unfortunately there isn't one fix all. Thanks again for your assitance!