Solved

near match joins

Posted on 2014-09-09
4
195 Views
Last Modified: 2014-09-22
Does Microsoft access have any way of joining 2 datasets whereby values in a column are not identical but very close. I am trying to join data from 2 tables on an address field, and there are a few instances where 1 or 2 characters are slightly off leading to the join failing. I didnt know if there was any way in SQL to do a "like" or similar join.
0
Comment
Question by:pma111
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 167 total points
ID: 40312159
Matching on addresses is extremely difficult due to misspellings, additional spaces, abbreviations of street type (Rd for Road; Ct or Crt for Court, ...).

I would start by replacing all of the duplicate spaces in the address field, something like:

UPDATE yourTable SET [Address] = Replace([Address], "  ", " ")
WHERE instr([Address], "  ") <> 0

And run this update query until no records are updated.

You might also try parsing the address field into Address#, StreetName, and Address Street Type.  You could do this in two separate queries, and then join the queries on those fields

You might also look at the Soundex algorithm which converts a word into an alpha numeric string so the Smith and Smyth would generate a match.  If you did this, then you might generate the Soundex value for the street name in each of the queries and join on the Address #, Soundex(StreetName), and StreetType.

Allen Browne has a VBA function that returns a Soundex value for a string passed to it.
0
 
LVL 36

Assisted Solution

by:PatHartman
PatHartman earned 167 total points
ID: 40312178
When I am cleaning up addresses, I also remove all punctuation.  Then I might standardize common abbreviations since sometimes people abbreviate and sometimes they don't so all street to st and all avenue to ave, etc.
0
 
LVL 21

Assisted Solution

by:Randy Poole
Randy Poole earned 166 total points
ID: 40312411
Here is a little VBA function to do some cleanup and allows you to implement a join on the address fields. I am attaching the mdb so you can take a look
checkaddress.accdb
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40312717
please post some data to better understand you fuzzy matching needs.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question