How to Deal with Name Changes over Time

[wilderfan]

My database involves corporations and their tax returns. Therefore, one of the tables lists the corporations. Another table lists the various tax returns for each corp over a period of time.

Sometimes, corporate names change. In fact, they can change many times over the life of a corp. And while the name may change, the underlying corp is still the same.

Does anyone have any experience / advice on how to deal with multiple name changes?

I imagine that end users may want to run a query to show the name history of a corp and when exactly changes occurred.

Thanks for any advice.

 

[CraigDolphin]

Actually, the same principle holds true for people. Some change names when they marry, divorce, go into hiding, etc And some have a variety of pseudonymns or aliases for reasons that I have yet to figure out.

The approach I once took was to create a separate table of PeopleNames that stored a FK from the Persons table's PK. Then I had another field called ValidFromDate that stored the date that the name was entered (user editable). Whenever the db needed to name the individual, it used the name with the most recent date. You could play around with different methods to choose a preferred name if there is more than one name for an individual.

This approach lets you store/search all the name variants, but lets you know which is the most recent, and gives you some idea as to which names were in use at a given time in the past.

Hope that helps.

 

[ajetrumpet]

Hopefully they are using access for this.

A basic underlying principle I suppose would be to create an extra table for name changes, and everytime the name of a company changes, maybe add a record to the table with some code? Then if users want to query the history, all they would have to query is this table. What do you think?

 

[ajetrumpet]

Sorry craig. same concept I think. you caught me.

 

[CraigDolphin]

Whee! Finally I got the answer posted before the other guy clicked post

No worries Adam, 9 out of 10 times I end up doing the same thing

 

[wilderfan]

Thanks, Craig. I'll try your suggestion and experiment with it.

 

[N1ck9141]

my surgestion

i havnt completely thought this through because im not completely sure what it isyour trying to do...

i would create a second table with this colums OLD name ,NEW name, tax detials (etcs )

the only new colum would be the old name one...

then create a from containing two text boxes and a button...

the first text is orignal name

second text box is new name

button changes them for you

(however and here is the clever bit )

you should be able to make it so it will copy all the information about corp (in text box 1 ) to your new table then rename it all to corp (in text box 2) with the new name on the live table

this will mean u always have a history of the data and the a current update table but also a nice form to do the changing for you saving alot of time which in buiness is always good

 

[neileg]

N1ck9141, you need to read up on normalisation. Your suggestion will work but it's not a good design and will only cope with two names. The suggestions from Adam and Craig will cope with an infinite number of changes and keep the history.

 

[wilderfan]

I've been trying to implement Craig's suggestion, but I've hit a brick wall. I'll try to describe my tables & forms briefly.
I have a Corporations table with 3 fields: CorpID (the primary key), Corporation, IncorpDate
I have a Corporations form with text boxes for Corporation and the Incorporation Date.
I have set up a new table called CorpNameHist with the following fields: CorpNameID (the primary key), CorpName, CorpID (foreign key), EffectiveDate, RecordDate
There is a one-to-many relationship between the Corporations table and the CorpNameHist table (utilizing the common field CorpID).
______________________________________________________
Prior to dealing with the Name Change "issue", I used to enter the corporate information through the Corporations form.
So I'm thinking that the Corporations table should continue to store the current Corporation name.
That would mean:
1. When I enter new records using the Corporations form, the information should be stored in both the Corporations table AND in the CorpNameHist table. [That is, from the Corporations form, the corporate name should be stored in Corporations.Corporation and CorpNameHist.CorpName; and the incorporation date should be stored in Corporations.IncorpDate and CorpNameHist.EffectiveDate]
2. To implement a name change, I need to create a new form where I use a combo box to select the current corporation name - from which of the 2 tables, I don't know - and then input the new corporate name and the effective date of the name change.
3. Besides storing the new corporate name in the CorpNameHist table, the existing corporate name in the Corporations table needs to be replaced with the new corporate name.
It seems to me, I'm going to be knee-deep in VBA code with the above exercise.
Is there a simpler approach?
If not, perhaps someone can forward me some sample VBA code.
Thanks for any help you can offer.

 

[Banana]

Are you using a query as a recordsource for your Corporation form? The query would be able to select the most recent name from Name table and supply it with rest of Corporation information from the Corp table for your form.

 

[wazz]

wilderfan, i can see where you're coming from. but i think you're thinking that the most important field in the corp table is the name field, but the main field is really the id field. in a way, the name is irrelevant (not really but technically, yes).

i think with this approach, you don't have to store the name twice, but when you need the name of a corp in a query or a form or whatever, you will always need the 'names' table *as well* in order to get the name (with the most recent date) that matches the corp id.