Solved

near match joins

Posted on 2014-09-09
4
189 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
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 34

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

914 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now