Lookup tables for *alike fields* but with different data types (text and number)

[skydivetom]

Experts:

I need some basic recommendations (**without going into the weeds**) on a very general/fundamental WRT "data transformation".

Allow me to provide some background first (please note that the info below is generic):
- I am working on a project that requires to migrate legacy data (various organizations with various sources) into a new (target) system.
- The various organizations (I should say "subsidiaries") have similar data.


- Organization_A (Org_A) has a field "Category Code". Org_A stores [Category Code] as *text* such as "Transportation Services" or "Utility Services".
- Organization_B (Org_B) also has a field "Category Code". However, right, wrong, or indifferent, they store their values also as "text" but they use coded values such as "001" or "002" instead.
- Organization_C (Org_B) also has a field "Category Code". And in this hypothetical example, they store their values as "numbers" such as "1" or 2".
- ... where "Transportation Services" AND "001" AND "1" (Org_A, Org_B, Org_C, respectively) will be translated into "SomethingElse_ABC" in the target system.

So, ultimately, I have the following in the 3 sources tables (Org_A | Org_B | Org_C):

- Transportation Services   | 001  |  1
- Utility Services          | 002  |  2

Next, lookup tables:
- For the data value transformation from "legacy system" to "target system", we are going to use **lookup tables**.
- So, for Org_A and Org B, I could create a field [SourceValue] with data type = "Text" and include 4 records:
1. Transportation Services
2. Utility Services
3. 001
4. 002

Then, as part of the data transformation, we could have a 2nd column with

   [SourceValue]            [TargetValue]
1. Transportation Services  SomethingElse_ABC
2. Utility Services         SomethingElse_XYZ
3. 001                      SomethingElse_ABC
4. 002                      SomethingElse_XYZ

However, given that Org_C stores their values as a "number", I cannot enter "1" and "2" (as 5th or 6th record) in the lookup table. So, at this moment, I am wondering if we end up with two (2) lookup tables where legacy data from Org_A and Org_B are added to "LookupTable_CategoryCode_Text" while Org_C's data should be added to "LookupTable_CategoryCode_Number".

So, again, I am curious about some *general recommendations* how to best handle *alike* fields but w/ different data types. Please do keep in mind the above is a dummy representation of the scenario. Also, keep in mind that we cannot change the legacy systems... ultimately, they are what they are! What I do have some input over is the process for transforming the legacy data into the new system.

So, here my question: which of these three solutions appears to be best?
1. Either use multiple lookup tables (for alike fields) but suffix them with "_text" or "_number"?
2. Use a Function (module) where I could use CASE statements and convert "Transportation Services" OR "001" OR "1" to be converted into "SomethingElse_ABC"? If so, can I mix and match text & numbers in the same function?
3. Or, upon receipt of the legacy data, should I convert Org_C's '1' (numeric) into "1" (text) and then expand the lookup table (text) to the following:

   [SourceValue]               [TargetValue]
1. Transportation Services     SomethingElse_ABC
2. Utility Services            SomethingElse_XYZ
3. 001                         SomethingElse_ABC
4. 002                         SomethingElse_XYZ
5. 1                           SomethingElse_ABC
6. 2                           SomethingElse_XYZ

Thank you in advance,
EEH

[NoellaG]

2 possible solutions:
add a new column to your mapping table: FieldType, so you can enter everything as a text in column Sourcevalue and use the second column to build the correct SQL clause. Or, as you state in option 3, convert the legacy data on receipt. IMO that would be the most simple solution.

[skydivetom]

NoellaG:

Thank you for the prompt response... just to make sure I understand you correctly:

Have 3 columns in the lookup table:
1. Source_CategoryCode_Text
2. Source_CategoryCode_Number
3. Target_CategoryCode

Then, depending on the source, use "Source_CategoryCode_Text" AND "Target_CategoryCode" for both Org_A and Org_B, while
using "Source_CategoryCode_Number" AND "Target_CategoryCode" for Org_C?

... in the event I would NOT to the data conversion upon receipt.

Thank you,
EEH

[ArviLaanemets]

In new DB, create a table e.g. tCategories: CategoryID (autonumeric), OrgID (numeric), CategoryCode (text), CodeType (numeric), CodeInfo (text).
Import category codes from all source tables into tCategories, following rules:
When source Category Code is text, it is saved into tCategories.CategoryCode as text, and CodeType is set to e.g. 1;
When source Category Code is numeric, it is converted and saved into tCategories.CategoryCode as text, and CodeType is set to e.g. 2;
tCategories.OrgID gets a value depending on source table, e.g. 1 for Org_A, 2 for Org_B, and 3 for Org_C;
tCategories.CodeInfo is left empty.

After all categories are imported, you have manually fill the field tCategories.CodeInfo with unique text which will be later used e.g. in combo boxes in your new database.

Now you can start importing other tables. Whenever imported table has a FK based on category code in source DB, you use table tCategories to find matching CategoryID (using OrgID, CategoryCode and CodeType for this), and insert it into according numeric FK field in target table.

When all source tables are imported, you can delete all columns from tCategories except CategoryID and CategoryInfo (unless you have to repeat import in future).

[NoellaG]

answer to #3:
table I suggest:
Source FieldType TargetValue
001 Text ABC
002 Text EFG
1 Numeric ABC
2 Numeric EFG


the second column is used to set the correct '''' signs when designing dynamic SQL

[orange]

Hi Tom,

This seems the next step in a topic you posted previously. The data dictionary module I sent can identify fields in tables. It could also print definitions/descriptions if they exist.

I know you don't want to get into the weeds, but I'm going to recommend this youtube segment - just to hear that you are not alone, and you may get some ideas of attacking the specific issue and/or communicating the scope of the issue to others/management.

I don't know the presenter and I have no knowledge nor interest in the software that was the topic of the conference/webinar. Alec highlights some issues that always occur when bringing/merging/reconciling disparate systems together.
I've been through a few of these and there is no right answer. Very dependent on information management discipline and practices of the organization.

Good luck.

[skydivetom]

ArviLaanemets -- do you happen to have an DB example based on the provided info?

Orange -- thank you... I'll check out the YouTube video.

[accesstos]

3. Or, upon receipt of the legacy data, should I convert Org_C's '1' (numeric) into "1" (text) and then expand the lookup table (text) to the following:

Hi Tom!

If you keep a separate table for each organization, with original types of legacy data plus the field [TargetValue], with a UNION query, you can retrieve all the appropriate info in a dataset.

SELECT CStr([CatCode]) AS SourceValue, TypeName([CatCode]) AS CodeType, TargetValue 
FROM Org_A 
UNION ALL 
SELECT CStr([CatCode]) AS SourceValue, TypeName([CatCode]) AS CodeType, TargetValue 
FROM Org_B 
UNION ALL 
SELECT CStr([CatCode]) AS SourceValue, TypeName([CatCode]) AS CodeType, TargetValue
FROM Org_C;

Is this close to your needs?

Cheers,
John

[skydivetom]

John:

Hmh, I'm not 100% confident this method will work for us. Let's expand based on actual data.

- Any of the organizations may have 100+ fields in their source data.
- Any of the tables may include thousands of records in each table.
- If I understood you correctly, I would have to essentially double the # of fields ("original types of legacy data plus the field [TargetValue]"). So, for any source field to be converted, I also would have to add the associated target field.
- Then, based on the example, for every populated record/value, I would have to determine the new target value.

Ultimately, my goal is to keep the source data as is and then use a query to derive the target value from the lookup table and convert the legacy data into current date (via make-table query).
At this time, I merely would like to keep the number of lookup tables at a minimum while (concurrently) not required to have 2 lookup tables (e.g., one for TXT and a 2nd one for NUM) for the same legacy field.

I apologize if I skimmed over w/ this part w/o providing the necessary details in the original post.

Cheers,
Tom

[orange]

Tom,

For clarity, this is a project to get all orgs onto the same system/database using your Target data and values;
OR
This is an effort to get something that will take disparate systems and convert the data in order to get some sort of consistent reporting/querying capability?

The existing systems will continue and you will merge them regularly for your needs
OR the existing systems will be dropped and the new system will be used by all?

[accesstos]

- If I understood you correctly, I would have to essentially double the # of fields ("original types of legacy data plus the field [TargetValue]"). So, for any source field to be converted, I also would have to add the associated target field.

I don’t see the problem on that; you need a placeholder for the target data – anyway.

- Then, based on the example, for every populated record/value, I would have to determine the new target value.
Ultimately, my goal is to keep the source data as is and then use a query to derive the target value from the lookup table and convert the legacy data into current date (via make-table query).

In any case, you have to determine the TargetValue for each unique legacy data in each field, so, imagine the tables of my example as tables with those unique values for each organization and the qunAll as the single lookup table. In other words, those tables could be temporary tables-in-the-middle of migration, perhaps as result of a SELECT DISTINCT query on original tables, and their usage could be only for the match-up of the values (old-->new).
What do you have in mind about this match-up of the different old legacy values to the same new (target values)?

At this time, I merely would like to keep the number of lookup tables at a minimum while (concurrently) not required to have 2 lookup tables (e.g., one for TXT and a 2nd one for NUM) for the same legacy field.

Either in one table or in three tables, the sum of unique legacy values to be determined is the same – anyway.

Cheers,
John

[skydivetom]

Orange / John -- I apologize for the delayed response.

Orange, pertaining to your questions:
Answer: **This is a project to get all orgs onto the same system/database using your Target data and value**

Answer: **The existing systems will be dropped and the new system will be used by all**

-----------------------------------------------

John -- I'll re-review the information you provided and see if that fits in an example DB.

BTW, I posted a related (but different) post at the following URL: https://www.accessforums.net/showthread.php?t=83240

Many "balls in the air" right now, trying to juggle w/o (hopefully) dropping a/any ball on the many activities. Thank you for your continued support/advice. I truly appreciate it!

[mike60smart]

Hi
Are you able to upload a zipped copy of the database?