Help with access solution needed

[Guest]

I've just got a task to compare to different access databases to see if there
are any adresses that match between the two databases. The two databases are
sorted in the same fields, adress, location, zip code and customer (and some
other unimportant ones). The problem is that the two databases are not
structured in any way, i.e. the adresses are for example main road 43, main
rd 43 and main rd. 43 the same is true about the other categories, there are
differences within the same database and between them.

What I would like to have is one database where you can see which database
the adress (or subject) origines from and to see how many of the same adress
there are at each adress. The problem is that each one of the databases
contains approx 250 000 lines (or adresses) so it is not possbile to adjust
the adresses to the same style (i.e. main road and main road) by hand.
Currently there are no relations etc between the databases as they are made
in two different "documents", i.e. it is just raw data of two companies that
has been put into the databases.

I would appreciate help a lot, thanks

 

[Pat Hartman\(MVP\)]

There are products that will do this matching for you. The one I used cost
several thousand dollars but was worth every penny. I was tasked with
consolidating company files for a multi-national corporation and some of its
subsidiaries. The data came from a variety of back end databases - DB2,
IMS, Oracle, Sybase, LotusNotes, etc. I imported it all into Access to do
the scrubbing. If the cleaning is important enough to your company that is
the route I would suggest. You can do some cleaning yourself though. Add
a new column to each table to hold the modified address since you don't want
to overlay the original address field. Then write code that looks for a
list of abbreviations and expands them
rd - road
st - street
ln - lane
etc.

You should also remove all punctuation and eliminate all multiple space
characters.

You can add additional columns to attempt to separate zip and city.

There is no silver bullet here. It is tedious and you'll need to know a lot
about the data to make intelligent choices.