near match joins

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.
LVL 3
pma111Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
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
Randy PooleCommented:
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
aikimarkCommented:
please post some data to better understand you fuzzy matching needs.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.