Handling Company Name/Ownership Changes in a Database (1 Viewer)

[EternalMyrtle]

How do you handle company mergers, takeovers and name changes in your database? This is meant to be a general theoretical question as well as a "how to" question.

My database is mostly for project management. I need to keep track of companies we work with, which are numerous and always in flux. When a company name or ownership structure changes, I need some way to handle this problem.

When a user selects the old company name, I want it to be updated to the new company but I do not want to completely remove the old company from the record. I am not sure if older projects should remain associated with the old company or not (if I do that, it seems to open a can of worms: I would have to know the exact dates of each turnover; also, what happens if the company changed its name/ownership during the course of a project?)

Right now, I am handling this is a cumbersome, semi-automated way that will be very bad in the long-run. It involves uses a macro (gasp!) that tells Access If CompanyID "X" is selected, then set value to "Y". The problem with this, of course, is that someone would have to enter each CompanyID number when a company name/ownership change occurs. This is clearly not the best method in the long-run.

I have a status field in my companies table (tblCompanies). If the Status is changed to "name/ownership change", I would like something to happen but what? Should the CompanyID be recorded somewhere? Should the new CompanyID be entered somewhere?

I am just not sure what the best way would be. Any advice?

 

[Isskint]

How you handle this is determined by the rules you define (or that the merged company defines as some may want the old name on old projects and the new name on new projects WHERE AS others want no further reference to the old name)

If you decide to change all occurrences of the old name with the new, it should just be a case of changing 1 field of 1 record (the Company Name field associated with that CompanyID). Thats a simple button on a form to record and update the name.

If you want to keep some as the old name and some as the new, I would then have another table to identify the merges. MergeID (PK) Autonumber | OldID (FK) number | NewID (FK) number | MergeDate Date | optional ChangeForGood Boolean
With that in place a simple query would identify if the name displayed should be the old or the new (or a subsequent change between Old and New)