Combining tables ID hitch

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Peter Lawton - 29 Jul 2005 11:18 GMT

I have just merged with another company and we want to combine our
databases. They are both Access 97 and each have a table with the same data
fields.

However both have primary fields using Autonumber starting with 1 which are
linked to other 'join' tables.

I would have been wonderful if one of us had started with 1000 but life's
not like that. My synapses are being tied in knots trying to think of a way
through without starting again from scratch. If someone can start me down
the right path for thinking this merger through I'd be most grateful.

pete lawton

peter@rottersgolfclub.co.uk

Reply to this Message

Nikos Yannacopoulos - 29 Jul 2005 12:10 GMT

Pete,

Take the database with the fewer records in the main table, and add one
more numeric field to the table; copy the table over to Excel, to fill
in this field with numbers starting 1000 (or ten thousand or whatever,
as long as you start higher than the highest ID field in the other
database). Then move the table back into Access, so the new field is
populated with the new numbers.

Then, add a similar field in the dependent table in the same database,
and use an update query to populate it with the same values as the
related records in the main table.

When you are happy that you have a new field to link the two tables on,
with no overlapping with the autonumber in the other database, drop the
original autonumber field and the related one in the dependent table,
and keep the new one only.

Now you can merge the two databases. After the merger, you could employ
a similar technique (new fields populated in Excel) to get a new, clean
sequential PK-FK field.

*BACK-UP* before you try anything!

HTH,
Nikos

Reply to this Message