Re-number ID field, but also its related ID field

[mecchaeric]

Situation: I have two Databases. One is called Companies, the other Contacts. Each company record has a LocationID. Each contact has a ContactID, but also has a LocationID so they are assigned/related to a particular company.

Fact: In the past, I have re-numbered ContactID, no problems, but that was ok, as LocationID in both databases were obviously unaffected. Basically I know how to re-number a field.

Problem: I will be merging some other database data into the Companies and Contacts databases. However, the there is a conflict with the LocationID's in the databases that are to be merged.

Question: So all contacts are still assigned/related to same companies, how do I re-number the LocationID in the Companies database, while also equally and accurately re-numbering the related LocationID's in the Contacts database?

I hope I decribed all this well.

Thanx in advance to all for any and all assistance.

Edited June 15, 2006 by Guest