Merging 3 databases into 1

[Chaga]

Hello everyone.
I have 3 similar databases containing multiple tables with different data, that I need to merge into one.
How can I do it while preserving data integrity since the main AutoID will change.
Let's say 3 tables

Contacts
Interviews
JobHistory

Interviews and JobHistory will have the ContactsID as FK but after I import Contacts this ID will change as it will append to the current one.
So how can I link the imported Interviews and JobHistory to the new IDs?

Thanks.

 

[CJ_London]

There are several ways of managing this requirement but the right one depends on whether this a one time exercise or one that needs to be repeated on numerous occasions. Please clarify

Also depends on whether or not you need to know which database the data came from and whether the same contact could be in different databases and if so, how would you identify these 'duplicates'

 

[Chaga]

There are several ways of managing this requirement but the right one depends on whether this a one time exercise or one that needs to be repeated on numerous occasions. Please clarify

Also depends on whether or not you need to know which database the data came from and whether the same contact could be in different databases and if so, how would you identify these 'duplicates'

I have a large data set that I split in 3 for data entering to speed things up so it will be (I hope) a one time thing.
I have 3 different people entering the data (each one with a different set, but there might be a couple of duplicates that we missed in the original set that I will delete whenever detected), and once all are done, I will merge, then use the final database for all future entries.
As for the database origin, I don't really care about preserving that info.

 

[CJ_London]

OK so a one time requirement.

Have to assume that contact names are not unique within a source db so suggest simplest way is in your new db is to include an extra field 'oldPK' which will be populated with the source table PK and you have a new autonumber PK.

1. link to your first db contacts table, and import to the new table contacts, populating oldPK with the sourcePK
2. ink to your first db interviews table
3. create an append query linking your new contacts table to your linked interviews table on oldPK=autoPK and update the interviewFK to to the new contacts autoPK
4. repeat 2 and 3 for jobHistory
5. set values in new contact table oldPK field to 0 or null
6. Repeat 1 to 5 for other two db's
7. When all done, remove the oldPK field

 

[Chaga]

OK so a one time requirement.

Have to assume that contact names are not unique within a source db so suggest simplest way is in your new db is to include an extra field 'oldPK' which will be populated with the source table PK and you have a new autonumber PK.

1. link to your first db contacts table, and import to the new table contacts, populating oldPK with the sourcePK
2. ink to your first db interviews table
3. create an append query linking your new contacts table to your linked interviews table on oldPK=autoPK and update the interviewFK to to the new contacts autoPK
4. repeat 2 and 3 for jobHistory
5. set values in new contact table oldPK field to 0 or null
6. Repeat 1 to 5 for other two db's
7. When all done, remove the oldPK field

Cheers mate, will give it a shot.