Merge databases in Access 2007

[Renald]

We have a research database which we use for interview purposes. These
databases consist of several questionnaires. The database contains a list of
customers, preloaded in the database. Once a customer is opened, all
questionnaires are completed and result in tables. Each customer visited gets
a unique ID, wich unique ID is linked with all answers. Now we have several
databases, each with the same basic structure. But with different customers.
However, since the ID's are generated in the several databases, some database
contain one and the same number for different customers.

Question, how can we merge these several databases into one, while we do not
loose any information, and the ID link with the customers is maintained?

I realize this is a rather complicated question, but hope to get the solution.

 

[gllincoln]

Hello Renald,

The simplest way (I think) may be to create new Master Table of Customers,
then create a new column in every table you wish to merge for the primary
key of the Master Table (I will call it MasterID here).

Hopefully - if the individual databases include a text name of the
customer, you can identify the individual customers by that field and run an
update query that updates the new MasterID column with the appropriate value
based on a join between the Master Table and the individual table on the
text name of the customer (or maybe phone number - whatever works).

Once you have populated the new MasterID column in an individual database -
then merging to the Master Database becomes relatively simple, right?

Hope this helps,
Gordon