Need to Merge 2 identical databases - with problems....

[magmike]

Both databases are identical - same structure, related tables, etc.
The difference is, they were used for different types of contacts. One
was for healthcare companies, and the other for all other types. I've
convinced my boss that it would be better to merge into one.

Of course, both main tables will have identical id (autonumbers)
numbers with different records attributed to that id number, For
example, in database1 id# 3456 would be attributed to Acme Painting,
while in database2, id# 3456 would be attributed to Quality
Healthcare. In addition, related tables would have the id# in them as
well.

Does anyone have experience with this scenario? This must be common,
especially with company megers.

Thanks in advance!
magmike

 

[KARL DEWEY]

Add a new number field to your second database tables and primary table in
database1. Update the new field with the highest autonumber in table 1 plus
the autonumber of that table. Do the same for all related tables.
Append the primary table from database 2.
Append secondary tables by joining the added number field and using the new
primary key.

 

[Preps69]

Magmike,
You will need to create a new id number for both data bases before merging
so you can still have the id# 3456 for database 1 and 2 but new id e.g 1200
for Acme Painting and 1201 for Quality Healthcare and then you will need to
add the new id numbers to the related table.
Hope that helps.

 

[magmike]

Magmike,
You will need to create a new id number for both data bases before merging
so you can still have the id# 3456 for database 1 and 2 but new id e.g 1200
for Acme Painting and 1201 for Quality Healthcare and then you will need to
add the new id numbers to the related table.
Hope that helps.








- Show quoted text -

Are you suggesting a joined id# like 3456-1201?

 

[Paul Shapiro]

For the main table in the new merged db, add a new attribute oldID. When you
import the old data, assign the current id to the oldID and let Access
create a new ID value for the imported data records. When you import the
child data into related tables, use the oldID to make the matches so you can
use the new ID in your child data. After processing the first old db, set
all the oldID values to null so they won't be used with the 2nd old db.

 

[magmike]

For the main table in the new merged db, add a new attribute oldID. When you
import the old data, assign the current id to the oldID and let Access
create a new ID value for the imported data records. When you import the
child data into related tables, use the oldID to make the matches so you can
use the new ID in your child data. After processing the first old db, set
all the oldID values to null so they won't be used with the 2nd old db.







- Show quoted text -

I've got everything on cue now - thanks a bunch. However, one last
problem...

Some records refer to other records in the same table when that other
record is the mother company.

So there is a field in MAIN table for OwnerCompany. In that field, is
the OldID for the owner company. I've been trying to write an update
query that will update that field based on the other records oldid and
change it to its newid but i can't seem to get it right. any ideas?

 

[magmike]

For the main table in the new merged db, add a new attribute oldID. When you
import the old data, assign the current id to the oldID and let Access
create a new ID value for the imported data records. When you import the
child data into related tables, use the oldID to make the matches so you can
use the new ID in your child data. After processing the first old db, set
all the oldID values to null so they won't be used with the 2nd old db.







- Show quoted text -

I've got everything on cue now - thanks a bunch. However, one last
problem...

And so that you understand:

Main table (the client table and the table that all other tables
relate to) - ProspectTable

Pertinent fields in ProspectTable:
ID - autonumber (this is a new id for the most recently imported data)
OldID - this is the old autonumber id field from the most recently
imported data)
Company - the name of the company
OwnerCompany - the ID of the company/record that is the mother company
or owning company of this record

and of course, other fields not pertinent to this issue.

NOW - the OwnerCompany field still contains the OldID number in its
field. I've been trying to write an update query that would change
OwnerCompany to the new ID of the OwnerCompany where
OldID=OwnerCompany. However, I can't seem to get it right. Any ideas?

And just for the record - the current records (not the records
recently imported) only number 3 that had any data in the OwnerCompany
field. Those have been recorded and can be changed after the update
query does its work.

Thanks (again) in advance!
magmike