Dear Experts:
I need some assistance with *streamlining* and/or *modifying* a working process using CASE statements (in a module). Please see attached database -- it contains only *extremely condensed sample data* but it mimics the actual process. Also, to make things easier, I have only included a subset of tables (and lookup tables). Allow me to summarize the current process:
1. Source table "tbl_MMAC":
- Includes 3 fields and 20 sample records
- Fields have data type = string and number
2. Lookup (LK) tables:
- LK table "LK_BLS_ACCIDENT_TYPE_MMAC" includes mapping information for [tbl_MMAC].[BLS_ACCIDENT_TYPE_MMAC]. Mapping info includes reference about "tier level" in field [TARGET_FIELD].
- LK table "LK_BLS_BODY_PARTID_MMAC" includes mapping information for [tbl_MMAC].[BLS_BODY_PARTID_MMAC]. Mapping info includes reference about "tier level" in field [TARGET_FIELD].
- Please note there's no mapping table for the 3rd field [tbl_MMAC].[HOSPITALIZED_ID]. This is ok given [HOSPITALIZED_ID] is not *tiered* (i.e., data will only be stored on level/tier 1).
3. APPEND query and Data Storage table:
- Query "qry01_MMAC" appends records (with converted values) into table [00_tbl_Master_Pk].
- I will come back to the "data value conversion" in a moment.
4. Module "02_ValueTransformation":
- While this process is working, this is where I would like to streamline my existing process.
- At the risk of repeating myself, let me re-emphasize this DB only includes sample data. My actual data includes nearly 100k records across 50+ fields.
- Anyhow, in order to generate the "Case" statements for the fields, I am using a spreadsheet to generate the source/target values in the correct VBA structure/syntax.
- At this time, please review the attached XLS "Value Transformation". Here you see three (3) tabs + 1 tab "Lookup".
- Ultimately, the DISTINCT values from [tbl_MMAC] are entered into column A.
- Column B:C contain information about [TARGET_TABLE] and [TARGET_FIELD], respectively. Please note that information in [TARGET_FIELD] is used in the APPEND query to determine the "tier-level" (e.g., '*_TIER1_ID' or '*_TIER2_ID' or '*_TIER1_ID').
- I will get back to the tier-level in a moment though...
Existing Process (in sequential order):
1. I get a new/updated source table [tbl_MMAC].
2. I identify the DISTINCT values for each of the 3 fields.
3. The updated data set may (or may not) have new DISTINCT values. If there are new DISTINCT values, I will have to include additional rows in the XLS to account for the legacy/target values.
4. For each DISTINCT value, I then use the 3 tabs in the XLS to generate the "Case" statements for values transformation.
5. Once XLS has been updated (if new values were identified or legacy/target values changed), I then copy/paste values in spreadsheet's column L into my ACCDB module "02_ValueTransformation".
6. Please note that step #5, does NOT replace the "Case Else" statement in each of the 3 functions.
7. I remove all records from "00_tbl_Master_Pk" and then execute the APPEND query. Ultimately, this process takes the "legacy data" from [tbl_MMAC] and converts the records into the target systems' values.
Ok, at this time, allow me to go back to the APPEND query:
a. As aforementioned, I have LK tables for 2 fields that need to be mapped on different tier level (up to 3 levels).
b. Let's review the expression in the query's, e.g., 2nd column/field:
- Given I linked the LK table [LK_BLS_ACCIDENT_TYPE_MMAC] to [tbl_MMAC], it looks up the source value from the LK table.Code:BLS_ACCIDENT_TYPE_MMAC_TIER_1_Alias: IIf([LK_BLS_ACCIDENT_TYPE_MMAC].[TARGET_FIELD]="INJ_INJURY_MECHANISM_TIER1_ID",(LK_BLS_ACCIDENT_TYPE_MMAC([tbl_MMAC].[BLS_ACCIDENT_TYPE_MMAC])),Null)
- If the value is found in [LK_BLS_ACCIDENT_TYPE_MMAC].[BLS_ACCIDENT_TYPE_MMAC], it then grabs the [TARGET_FIELD] value to identify the tier level.
- For example, source value = "10" indicates I want to move the converted value into "INJ_INJURY_MECHANISM_TIER2_ID" (2nd tier).
- Alternatively, source value = "11" indicates I want to move the converted value into "INJ_INJURY_MECHANISM_TIER1_ID" (1st tier).
- If the 2nd column "BLS_ACCIDENT_TYPE_MMAC_TIER_1_Alias" = "INJ_INJURY_MECHANISM_TIER1_ID", I use the value; otherwise, it sets it to Null.
- I use these IIF statements only for those 2 fields where mapping has to occur on tiered levels.
- Thus, for field [HOSPITALIZED_ID] I do NOT use the IIF; instead, I use expression: "HOSPITALIZED_ID_Alias: (TD_HOSPITALIZED_ID([HOSPITALIZED_ID]))" which converts, e.g., "0" into "N".
Now, here's what I need some help with:
- My goal is to eliminate the need for the XLS.
- That is, as indicated, I use the XLS purely for generating the Case statements.
My question(s):
- Is there another way to streamline the process so that I do NOT need to use the XLS for generating/updating the Case statements? If so, what would that be?
- I do NOT know whether or not I can eliminate the need for the Case statements in the ACCDB; if the Case statements are still necessary for value transformation, then generating the Case statements within a query would be already an improvement.
- Ideally though, I would like to eliminate the Case statements entirely but still perform the value transformation (incl. moving values into the correct _Tier1 or _Tier2 or _Tier3). Could this be programmed?
I'd welcome any specific recommendations which might allow me to improve the current process.
Cheers,
Tom