Solved

near match joins

Posted on 2014-09-09
4
187 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
please post some data to better understand you fuzzy matching needs.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

772 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

13 Experts available now in Live!

Get 1:1 Help Now