Combining databases - Common People table, many other tables.

[WDSnews]

I've had users at two different locations of a single company using two
different databases. One database was used to track assets and their
details. The other was used to track people and their details. From time
to time, I've exported the changes in the People table of the People
database to append to a people table in the Assets database. So, the people
table is in common, with consistent field names and IDs in both databases.
It's called People in both databases.

Now it's time to marry the two databases and my concern is that the Assets
database has a lot of relationships built up with its local version of the
People table.

The Assets database has not yet been split. The People database is split
with tables in one MDB file and front end items in a different MDB file.
The Assets MDB has 19 tables and the People MDB has over 40. Both have a
complex web of relationships.

What strategy would you use to combine the two databases? Is it possible to
maintain the table relationships?

 

[KARL DEWEY]

Here is a stab at it IF they only have the people table in common.
BACKUP THE DATABASE. BACKUP THE DATABASE.

Decide which MDB will house all the data. For this discussion I assume that
Assets will migrate to personnel database. I assume the people table in each
have a primary key and they may differ for some individuals.
Run an unmatch query to see if there are anyone in assets not in personnel
MDB. Append so that personnel MDB has everyone.
In the personnel MDB create a table name Asset-People like this (I am
guessing at your field names) --
AssetID - primary key from asset MDB
PeopleID - primary key from asset MDB
FName - text
LName - text
MI - text
Suffix - text
DOB - DateTime

Populate it with people table from personnel MDB. Create an update query
and update the AssetID field from asset MDB.

Import the table structures of all the asset tables. Set the relationships.
Append the records from asset MDB using the Asset-People table as translator
in your queries.

VERIFY! VERIFY!
Good luck.

 

[a a r o n . k e m p f]

anything with that many tables-- you should do it right instead of do
it twice.

Move to SQL Server

 

[bcap]

Ignore Kempf, he is an idiot, a liar and a convicted criminal.

 

[Elwood P Dowd]

Ignore Kempf, he is an idiot, a liar and a convicted criminal.

Not even my friend Harvey could have said it better.

On the other hand, Harvey thinks there may be hope for kempf, yet. But,
well, you know pookas, they are optimists about everyone.

El