UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Changing Primary Id?, Access 2016    
 
   
rusty1969
post Jul 9 2018, 07:26 PM
Post#1



Posts: 11
Joined: 11-March 18



If I have a table for Company with:

CompanyID ----- CompanyName
ABC ----- ABC Company
DEF ----- DEF Company
GHI ----- GHI Company

Then I have an order table with a primary key that get populated with order number, companyid, year... ex 001_ABC_2018

OrderID ----- CompanyID
001_ABC_2018 ----- ABC
002_DEF_2017 ----- DEF
003_GHI_2016 ----- GHI

if the ABC company buys/takes over the GHI company, how do i handle that in the order table without just deleting GHI and replacing with ABC? I don't care that the OrderID still contains the original code. would i....

CompanyID ----- CompanyName ----- PreviousID ----- active
ABC ----- ABC Company ----- ----- Y
DEF ----- DEF Company ----- ----- Y
ABC ----- GHI Company ----- GHI ----- N

The above would create a dup in primary key. i suppose i could have an auto number as the primary key? I want to be able to query for ABC and have all records there, even those when they were GHI. I'm guessing i would add the above "Active" field, because I have a drop down field of companies and I would include only the active ones.

OrderID ----- CompanyID
001_ABC_2018 ----- ABC
002_DEF_2017 ----- DEF
003_GHI_2016 ----- ABC

Is this the best way to handle this?? Or am I missing the simple answer?!

Go to the top of the page
 
GroverParkGeorge
post Jul 9 2018, 08:24 PM
Post#2


UA Admin
Posts: 33,747
Joined: 20-June 02
From: Newcastle, WA


The very simplest way would be to change the company name ONLY. Nothing else needs to be done. That's really what having a primary key is all about. If it's a surrogate, then that's especially true. That is, in fact, why we commonly use AutoNumbers.

In this case, simply replace GHI Company in the Company Name field with "New Company Name" in the Company Name field. The primary key GHI still points to all of the old orders and will point to all of the new ones as well. Again, one of the most important things about Primary Keys is that they do not change over time, except for certain rare situations, I suppose.

However, do you need a history of the company's names at various points? That's a different rule that might lead to a different design. If you do, then you need a table of Company Aliases to store that history. It can be very short as companies tend not to change names that often.
Go to the top of the page
 
zaxbat
post Jul 9 2018, 08:36 PM
Post#3



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


What you are proposing is the logical initial attempt that most peeps come up with, but it is less than ideal. A more commonly accepted approach is to keep a historical table of names with date ranges (indicating when the name applied to the company) that have a foreign key back into the actual company table
Go to the top of the page
 
projecttoday
post Jul 9 2018, 08:50 PM
Post#4


UtterAccess VIP
Posts: 10,150
Joined: 10-February 04
From: South Charleston, WV


Why do you have to do anything? Can't you just enter the new orders under the acquiring company? Leave the old orders the way they are.

QUOTE
The above would create a dup in primary key.


You could change the primary key to a non-unique index. On the customers-entry screen, add code to check for an existing company id on new entries and if found send the user a warning message.
Go to the top of the page
 
rusty1969
post Jul 9 2018, 09:27 PM
Post#5



Posts: 11
Joined: 11-March 18



I think I have to change old ones so I can query for all without them coming up as two separate companies, i guess i'd have to group on name, and not code. I usually try not to do that. So i guess I'm just asking if there is something really wrong with doing it this way with having an 'original' code field with an active/not active tick?

CompanyID CompanyName OriginalID Active DateBegin DateEnd
ABC ----- ABC Company ----- ----- y ----- 7/9/2018
DEF ----- DEF Company ----- ----- Y ----- 7/8/2018
ABC ----- GHI Company ----- GHI ----- N ----- 7/1/2018 ----- 7/9/2018
Go to the top of the page
 
GroverParkGeorge
post Jul 9 2018, 10:23 PM
Post#6


UA Admin
Posts: 33,747
Joined: 20-June 02
From: Newcastle, WA


I rather think that, if it is the same company operating under a different name, then you DO NOT change any of the primary or foreign keys in any of the tables.

As a couple of us have suggested, you may incorporate a DBA, or Alias table to handle the history of company names over time.

But the LAST thing you want to do is break the integrity of the Primary and Foreign Key constraints on the existing tables by trying to manipulate those keys. That way lie sand traps and thickets.
Go to the top of the page
 
projecttoday
post Jul 9 2018, 11:06 PM
Post#7


UtterAccess VIP
Posts: 10,150
Joined: 10-February 04
From: South Charleston, WV


Then I would go with a translation table. It seems to me that altering your business records after-the-fact could be problematic. Have you done this in the past? A translation table could be called into play only when needed. I'm guessing that they want these orders combined for management reporting. The people on the lower level who handle the orders might have a different perspective.

I imagine that there are only a handful of such customers (not that that eliminates the need for a solution).
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th October 2018 - 03:34 PM