Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Help with access solution needed

Discussion in 'Information Technology' started by Andreas, Jul 28, 2006.

  1. Andreas

    Andreas
    Expand Collapse
    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
     
  2. Loading...


  3. Pat Hartman\(MVP\)

    Pat Hartman\(MVP\)
    Expand Collapse
    Guest

    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.

    "Andreas" <Andreas@discussions.microsoft.com> wrote in message
    news:A26900EF-89D6-43A7-A6CC-0798EE360675@microsoft.com...
    > 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
     

Share This Page