Compare and/or merge data from one database to another

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TimR - 24 Feb 2007 23:11 GMT

I am very new to Access. I have two separate mailing list Access files with
the same or similar info in different sort orders and/or info in the same
fields. I would like to make on of them a "master"

How can I check DB "B" for the a street address field....and if there is a
match with DB "A"...then import the 'first name' field from "B" to the
matching record in "A"

once I get this working...can I automate the whole process in one operation
as DB "B" changes and/or is updated ?

Thanks, Tim

Reply to this Message

Tom Wickerath - 25 Feb 2007 09:43 GMT

Hi Tim,

For a one-time merge, check out this recent thread:

http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public
.access&mid=82657434-ccff-47a3-ad09-bf2beb466518&sloc=en-us

If you *really* need to have two separate databases, which you want to
periodically synchronize, then you should implement replication. You should
only try implementing replication on a *copy* of your database, not on the
original. I am not a replication expert, but I do advise you to download a
copy of the replication white paper from Microsoft:

ACC2000: Jet 4.0 Replication White Papers Available in MSDN Online Library
http://support.microsoft.com/?id=190766

Here is a KB article that may also be helpful:

ACC2000: How to Make a Database Replicable
http://support.microsoft.com/?id=208774

Although both titles include "ACC2000", the information should be equally
valid for Access 2002 and 2003. I would also head on over to Michael Kaplan's
web site and read everything you can find on replication:

http://www.trigeminal.com/usenet/usenet.asp?1033

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

> I am very new to Access. I have two separate mailing list Access files with
> the same or similar info in different sort orders and/or info in the same
[quoted text clipped - 8 lines]
>
> Thanks, Tim

Reply to this Message

TimR - 25 Feb 2007 14:01 GMT

Thanks Tom...being to Access I guess I should have been more precise...the
two DB's are not the same...there are differences in record numbers...filed
names...etc. I really need to only import *once* two or three fields from
"B" to "A" based on whether the street address filed matches.

After that I can manually adjust "A" when changes occur in address
info....Can this be done without using something like Total Access Detective
?

Also...thanks for the link to the Office forums...I'm sure I'll find a lot
of the same issues I am/will be having there !

Tim

> Hi Tim,
>
> For a one-time merge, check out this recent thread:

http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public
.access&mid=82657434-ccff-47a3-ad09-bf2beb466518&sloc=en-us

> If you *really* need to have two separate databases, which you want to
> periodically synchronize, then you should implement replication. You should
[quoted text clipped - 35 lines]
> >
> > Thanks, Tim

Reply to this Message

Tom Wickerath - 25 Feb 2007 20:23 GMT

Hi Tim,

> ...based on whether the street address filed matches.

What do you think the chances are that street addresses entered by humans
are going to be exact matches? I'd say slim to none. For example, my address
could be entered in any of the following ways. The Post Office will be able
to deliver my snail mail in each case, but these are different addresses to a
computer:

851 NE 21'st Place
851 NE 21st Place
851 NE 21st PL
851 NE 21st PL.
851 NE. 21'st Place
851 N.E. 21'st Place
851 N. E. 21'st PL.

> Can this be done without using something like Total Access Detective?

Sure, as long as you have an *exact* match. But, as I've indicated above,
your chances of having exact matches are not so great in my opinion. You
would likely need to go with some algorighm that was modified for street
addresses, such as Soundex or the Levenshtein distance methods. I don't know
if these can be modified to work with address type data--you'll need to
experiment to determine this for yourself.

See Access MVP Doug Steele's past article on this, available here:
http://www.accessmvp.com/djsteele/SmartAccess.html

April 2005: "Close" only counts in horseshoes...and databases (467 KB)
A couple of techniques to help determine when entries are "close enough" to
be considered the same

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

> Thanks Tom...being to Access I guess I should have been more precise...the
> two DB's are not the same...there are differences in record numbers...filed
[quoted text clipped - 9 lines]
>
> Tim

Reply to this Message

TimR - 27 Feb 2007 20:16 GMT

Thanks again Tom...I did some checking and both DB's are formatted with USPS
required postal formatting. I did a random check of dozens of records and
found no differences. Doesn't mean there are not in the 3000+ records...but
I think it would be worth a try ?

> > Can this be done without using something like Total Access Detective?
>
> Sure, as long as you have an *exact* match.

can you direct me to a site or link that will give me some idea on how to
set up a query to check the two data base address columns/fields...and then
replace the first name field in DB " A"with the first name field in the
matching address row of DB "B"

Thanks, Tim

> Hi Tim,
>
[quoted text clipped - 50 lines]
> >
> > Tim

Reply to this Message

Tom Wickerath - 27 Feb 2007 22:12 GMT

Hi Tim,

To replace the first name field in DB "A" with the first name field in the
matching address row of DB "B", should only require a simple update query,
as long as you have address data that is matching. Create a standard SELECT
query that involves the two tables. Join the address fields. Add the first
name field in DB "A" to the QBE grid. When you run the query, you should
hopefully see your 3000 + records. If so, convert the query to an update
query. In the Update To row, specify the name of the table and field for the
corresponding first name value in database "B". Something like this:

Field: FirstName <---From database "A"
Update To: [TableName].[FieldName] <---From database "B"

Take the normal precautions of making a backup before you run any action
query.

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

> Thanks again Tom...I did some checking and both DB's are formatted with USPS
> required postal formatting. I did a random check of dozens of records and
[quoted text clipped - 11 lines]
>
> Thanks, Tim

Reply to this Message

TimR - 28 Feb 2007 00:04 GMT

Tom...Thanks so much for your help/patience...Took a little fiddling but i
got it to work...Looks like it worked fine. (Remember..I never even opened
access until a week ago )

Thanks, again...

Tim

> Hi Tim,
>
[quoted text clipped - 35 lines]
> >
> > Thanks, Tim

Reply to this Message

Tom Wickerath - 28 Feb 2007 04:43 GMT

Hi Tim,

Excellent!

> (Remember..I never even opened access until a week ago )

I'm not sure how I would have known that, because you didn't mention this
earlier. I have a Word document that I'd like to offer you as a download.
About once every month or two, I post an updated copy of this document. I
call it "Access Links.doc". Basically, it's filled with lots of useful links
and other tidbits. I consider the first three pages, along with two
hyperlinks shown in red font on page 4, very important for anyone working
with Access. This includes topics such as naming conventions, reserved words,
database design, best practices for keeping your system up-to-date (ie.
install all service packs), reference problems, introduction to locking
database files (*.ldb), etc. You can download a copy here:

http://home.comcast.net/~tutorme2/samples/accesslinks.zip

Here's a few other documents that you might want to grab at the same time:

http://home.comcast.net/~tutorme2/samples/ActionQueryExamplesWithSetWarnings.doc
http://home.comcast.net/~tutorme2/samples/tmpwrkdb.zip
http://home.comcast.net/~tutorme2/samples/unionqueries.zip
http://home.comcast.net/~tutorme2/samples/customdialogbox.zip

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

> Tom...Thanks so much for your help/patience...Took a little fiddling but i
> got it to work...Looks like it worked fine. (Remember..I never even opened
[quoted text clipped - 3 lines]
>
> Tim

Reply to this Message