Update Record ID to another Record ID in the Same Table and Update Related Records

[EternalMyrtle]

Hello!

I have a table called tblCompanies. When a company acquires another company, I need a method by which the acquired company's CompanyID (PK) can be updated to the new company's CompanyID (PK). I also need to be able to update all related CompanyIDs (FKs) to the new value in related tables.

In cases in which the new company does not have an existing record, there is no problem: the company name simply gets changed to the new company and the existing CompanyID is maintained. I then use an audit table and Track Changes function to keep track of the company name data and a union query to keep the old names in the selection lists.

The problem is when both companies already have existing records in the table.

So, let's say I have records for Company A and Company B. Company A merges with Company B and Company B is now the main record. What is the best, simplest and easiest way to update the CompanyID (PK) from A to B and change the CompanyID (FK) to the new value in all related tables?

I am envisioning a pop-up form that directs the user to select the new company and then an update query happens behind the scenes... but exactly how does the criteria for the update query get selected and how do all the related tables get updated? My vba skills are pretty basic, will I need extensive coding to do something like this?

I hope this makes sense. Thank you for your time.

 

[Mr Smin]

I think you should avoid changing PKs and in any case, PKs must be unique so you can't have two companies sharing one PK.

If the company being acquired ceases to exist as an independent entity, you could run update queries which replace all FK instances with the PK of the acquiring (still extant) company. You could then either delete the row for the company being acquired, or you could have an 'acquired by' column with the PK of the acquiring company, which will also act as a flag to say the acquired company no longer exists.

If a merger creates a new corporate entity, you may want to have a new row for the new entity and update FKs for both old entities to the PK of the new one.

 

[EternalMyrtle]

Thanks for your reply.

Yes, perhaps I was not clear enough: I do not want two companies to have the same PK, I just want to update the acquired company's record data to the new PK's record and delete the old company record. I will use audit and track changes to keep track of the old company's name.

So yes, the big challenge is determining the new PK and updating all of the FKs in related tables to the new PK.

So, you rephrased my problem and clarified (thank you for that!) but what I really want to know is how to do this so that it is seamless for the user? I can do it manually, of course, but that is not very useful.

 

[nanscombe]

It may be a daft question but ... would you ever need historic data?

Would you need to know information about which record were related to the old company only?

If so you may wish to think about adding a field to hold the original CompanyId so you could look back if needed.

 

[EternalMyrtle]

Pat: thank you! Your solution is so much simpler and more logical than what I was attempting to do.

One more question: I am not exactly sure how to group the merged records with the parent company records. Is there a simple way to do this that I am not considering?