You can start by creating links to the tables in both of the files in a new
empty .mdb file. That way you'd have access to both sets of tables
independently via the new file.
You can view (but not edit) the data from both tables simultaneously by
means of union queries. Lets take a very simple example and assume that you
have linked tables Contacts_1 and Contacts_2, in which case you could view
the rows from both with a query such as:
SELECT Contact
FROM Contacts_1
UNION
SELECT Contact
FROM Contacts_2
ORDER BY Contact;
A UNION operation suppresses duplicate rows so if the same contact is
represented in both tables the UNION query would return jut one row for that
contact.
To merge the data you could create a blank table Contacts in the new file
and then base an append query on the union query to insert rows into the new
Contacts table. Add an autonumber ContactID to the new table as its primary
key.
You can merge rows from referenced tables in exactly the same way, e.g. you
might have linked tables Cities_1 and Cities_2 which are referenced by
Contacts_1 and Contacts_2 respectively.
Where it begins to get tricky is in populating a CityID foreign key column
in the new Contacts table so that it references the correct rows in Cities.
To do this you need to join the original tables and the new tables so that
the correct CityID values from the new Cities table are inserted into the
relevant rows in the new Contacts table. You start with another union query:
SELECT Contact, City
FROM Contacts_1 INNER JOIN Cities_1
ON Contacts_1.CityID = Cities_1.CityID
UNION
SELECT Contact, City
FROM Contacts_2 INNER JOIN Cities_2
ON Contacts_2.CityID = Cities_2.CityID;
However, because a union query is not updatable any query you base on it
won't be updatable, so first you have to fill a temporary table,
ContactCitiesTemp say with columns Contact and City, from the above union
query:
INSERT INTO ContactCitiesTemp ( Contact, City )
SELECT Contact, City
FROM qryContactCitiesUnion;
You can then populate the CityID column in Contacts with:
UPDATE (Cities INNER JOIN ContactCitiesTemp
ON Cities.City=ContactCitiesTemp.City)
INNER JOIN Contacts
ON ContactCitiesTemp.Contact=Contacts.Contact
SET Contacts.CityID = Cities.CityID;
Hopefully the above will, while obviously not matching your scenario, serve
to illustrate the principles involved. I should emphasise that it is a
simplified example; you'd be unlikely to have a single Contact column for
instance, but more likely FirstName and LastName columns at least, so the
join in the last query above would be on both columns. Also names are not
necessarily unique so other columns might need to be brought into play in the
join to ensure correct matches. Similarly city names are not unique so other
columns might be necessary in the join, e.g. county or state. Finally the
above assumes that the data is consistent between the two source databases
and that if Contact A is recorded in City B in one, they are similarly
recorded in the other, otherwise you'd need to resolve the conflict.
Ken Sheridan
Stafford, England
