How to switch lookup table to new one and preserve old data?

[bigalpha]

We have a lookup table that has a list of CLIN numbers and their costs. The contract that governs those CLIN numbers and costs will be changing to entirely new numbers. Unfortunately, I still need to have the old and new CLIN numbers linked to the other tables.

Will I need to merge all the CLIN numbers into one lookup table, or can I do it from two lookup tables?

TIA

 

[CJ_London]

If I have understood you correctly, you can do it in one table if you want to:

Lets say your current table looks like this:

ID CLINno  Costs
1  a1         2.20
2  b3         3.23
3  x95        7.57

Add a new column, we'll call it CLLink of type long, indexed no duplicates then populate as follows

ID CLINno  Costs CLLink
1  a1         2.20
2  b3         3.23
3  x95        7.57
4  newa1    2.20    1
5  newb3    3.23    2
6  newD45f 4.21
7 newX95   7.57    3

CLink points back to the original record and you can also determine the new record by using criteria CLink=ID

 

[bigalpha]

Okay. After getting more information from my boss about the new CLIN list, it looks like the new list is going to be totally new and different. I was unaware that the list was changing so drastically.

So this means that I can't do a 1:1 for each CLIN line item. Before there were like 50 now there are 250.

 

[Cronk]

Add a second column called ContractID, give your first set of CLIN numbers a contractID of 1, and for your new set, 2.

 

[bigalpha]

Add a second column called ContractID, give your first set of CLIN numbers a contractID of 1, and for your new set, 2.

So combine both sets of data into one lookup table and delineate between the two by using contract number?

 

[Cronk]

Yes, that is what I'd do. It's very scalable so will provide for as many contracts as you could conceive having.