?
Solved

near match joins

Posted on 2014-09-09
4
Medium Priority
?
199 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 48

Accepted Solution

by:
Dale Fye earned 668 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 38

Assisted Solution

by:PatHartman
PatHartman earned 668 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 664 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 46

Expert Comment

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

Featured Post

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

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

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

741 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