Joining Like Fields in Access Query

[Lynch225]

Hello All,

I have 2 tables from 2 different data sources, where unfortunately the only fields able to be joined are street address, city, and state. Of those fields, obviously street address would work best. Unfortunately, since the 2 tables are from different data sources, one of which is from manual entry of POs, the street address will only join correctly about 50% of the time.

Ex.

Table1
Address | City | State
100 Main St | New York | NY
50 East 5th Ave | New York | NY

Table2
Address | City | State
100 Main Street | New York | NY
50 E 5th Ave | New York | NY

I have tried an inner join where Table1 Like Table2 & "*", but I either get 0 results (using the above example) or 4 results.

Any ideas if this is possible?

Thanks!

[zmbd]

Using a very simple database I think we can set you on the right path. The only difference will be that you will have to link the the two databases togeither, either within one or the other, or in yet a third.


In the attached there is a query, Qry_Using_Like_wild
DO NOT OPEN it using the GUI editor, it will puke.
Instead, run the query to see the results, and then right-click and select the SQL view.

First, Take a look at the two tables.
There are two forms, one that shows the info from the longname table and the other based on Qry_Using_Like_wild

What I've done is started out with a query like, qry_using_equal, by opening the standare GUI-Editor, adding the tables, and making the inner join.

I then switched to SQL view, changed out the equal sign in the join clause for the "Like" operator and appended the "*" wildcard.

Now this worked for my simple dataset; however, for yours there may need to be a more complex set of wildcards to get the best matching.

Once you have that down, go find someone that will help you to establish a standard dataentry form or to have common primary key between the two databases that will allow a much more effecent means of merging/handling the dataset.

I'm sure there's a better way to do this and either Rabbit or Neopa or one of the others should be along shortly to advise.

[NeoPa]

This situation is, as Z says (but I'll paraphrase), to be avoided like the plague!

The pitfalls are numerous and serious. That said, there are approaches that can help reduce the problem, but at the cost of losing information. For instance, one thing that may work is to preprocess the data with a routine that converts all common abbreviations to their full counterparts. That way "Station Rd" will match "Station Road".

The best idea, of course, is never to get into this sort of situation in the first place ;-)

[zmbd]

Neo, I was trying to be nice... plagues perhaps a little understated: think; ebola, or mustard-gas, golfing in the middle of landmines, locust, fire and ice from the sky, 40 days and nights of rain.

and with that my hopes for a good easy solution start to crash in the event horizon of the nearest super-massive-blackhole.

[Lynch225]

Haha, I completely agree (and thank you for the honesty). Eventually this process will be changed to have each store have its own unique number, which will solve this nasty problem. Unfortunately, as this is a new role I am in and there is an extremely time sensitive nature to getting this information together, I do not have the time I would like to fix the root cause of the problem.

I did manage to put a quick fix in by adding a zip code field in each data source and joining those fields. Then in the criteria for the address, I used:

Like '*' & Mid(Table2.Address,Instr(Table2.Address," "),8) & '*'

This allows at least a partial match in address, starting with the street name. This is a temporary fix that has solved all of my issues so far, but unfortunately I know there will be cases where this join may not work. It may be a used gas mask, but hopefully it prevents most toxic chemicals from entering my lungs :)

Thanks for the responses!

[NeoPa]

I hear you Lynch. Many of us have been there. I even had to work with such data coming from three data sources where only the one that I managed was remotely reliable. That's when I thought out the conversion approach that cancelled out most of the more common aliases for address and company terms. It worked pretty well but was never 100% reliable.

Good luck. It sounds like you're as much on top of it as can be reasonably expected.