Challenge! Streamline process by *removing* need for CASE statements

[skydivetom]

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:

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)

- Given I linked the LK table [LK_BLS_ACCIDENT_TYPE_MMAC] to [tbl_MMAC], it looks up the source value from the LK table.
- 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

[kd2017]

You've got a big convoluted solution to what seems like a pretty straight forward problem... unless I'm missing something.

Maybe try something like this UNTESTED code below. This would replace your LK_BLS_ACCIDENT_TYPE_MMAC function.

Public Function LK_BLS_ACCIDENT_TYPE_MMAC_v2(BLS_ACCIDENT_TYPE_MMAC As Variant) As Variant
On Error GoTo ErrHandler
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim qry As String
    Dim rslt As Variant
    
    '1. set the default value
    rslt = ""
    
    '2. First check the lookup table for the TARGET_PK_FK
    qry = ""
    qry = qry & "SELECT TOP 1 LK_BLS_ACCIDENT_TYPE_MMAC.TARGET_PK_FK " & vbCrLf
    qry = qry & "FROM LK_BLS_ACCIDENT_TYPE_MMAC " & vbCrLf
    qry = qry & "WHERE (((LK_BLS_ACCIDENT_TYPE_MMAC.BLS_ACCIDENT_TYPE_MMAC)=""" & BLS_ACCIDENT_TYPE_MMAC & """));"
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(qry, dbOpenSnapshot)
    
    '3.a Make sure the query found something
    If Not (rs.BOF And rs.EOF) Then
        '3.b Make sure what it found wasnt an empty string
        If rs!TARGET_PK_FK <> "" Then
            rslt = rs!TARGET_PK_FK
        End If
    End If
    
    'always close something if you 'open'ed it
    rs.Close

ExitHandler:
    'clean up the objects we defined
    Set rs = Nothing
    Set db = Nothing
    
    'finally, return our results
    LK_BLS_ACCIDENT_TYPE_MMAC_v2 = rslt
    
    Exit Function
    
ErrHandler:
    MsgBox Err.Description, vbInformation, "LK_BLS_ACCIDENT_TYPE_MMAC_v2 Error #:" & Err.Number
    Resume ExitHandler
    
End Function

You could simplify this further by replacing step 2 within my function with a dlookup (I'd personally use elookup) and thereby eliminate the need for the rs and db objects.

Public Function LK_BLS_ACCIDENT_TYPE_MMAC_v3(BLS_ACCIDENT_TYPE_MMAC As Variant) As Variant
On Error GoTo ErrHandler
    Dim rslt As Variant
    
    '1. First check the lookup table for the TARGET_PK_FK
    rslt = DLookup("TARGET_PK_FK", "LK_BLS_ACCIDENT_TYPE_MMAC", "BLS_ACCIDENT_TYPE_MMAC=""" & BLS_ACCIDENT_TYPE_MMAC & """")
    
    '2. Validate tthe results of the lookup
    If IsNull(rslt) Then rslt = ""

ExitHandler:
    'finally, return our results
    LK_BLS_ACCIDENT_TYPE_MMAC_v3 = rslt
    
    Exit Function
    
ErrHandler:
    MsgBox Err.Description, vbInformation, "LK_BLS_ACCIDENT_TYPE_MMAC_v3 Error #:" & Err.Number
    Resume ExitHandler
    
End Function

[skydivetom]

kd2017 -- I have done some *initial* testing for field "BLS_ACCIDENT_TYPE". I will further review the code but at first glance, it looks like this VBA code does exactly what the CASE statement do. Awesome!

I will copy the process for the 2nd field [BLS_BODY_PARTID]. I will let you know how that works out.

In the meanwhile, how would this code address any values that may exist in the source table (but have NOT been mapped -- for whatever reason -- in the LK table)?

Cheers,
Tom

[kd2017]

AND you may be able to eliminate the vba all together with something like this:

SELECT
    Nz(
        DLookup(
            "TARGET_PK_FK", 
            "LK_BLS_ACCIDENT_TYPE_MMAC", 
            "BLS_ACCIDENT_TYPE_MMAC=""" & BLS_ACCIDENT_TYPE_MMAC & """"
        )
    ) AS LK_BLS_ACCIDENT_TYPE_MMAC
FROM tbl_MMAC

[skydivetom]

kd2017 -- hmh, very interesting approach!!

How would the latter be put together for the 3 fields currently used (incl. the tier level for 1st and 2nd field)?

Cheers,
Tom

[Gicu]

Hi Tom,

Could you please supply us with a couple of sample source tables as per step 1. in "Existing Process"? The data in the provided one (tblMMAC) doesn't seem to match the lookup tables and has no PK. How\where do you build the lookups? To make this process streamlined you'd need to step away from writing individualized VBA code and make it more generic by providing a source name not only a target.

Cheers,

[kd2017]

In the meanwhile, how would this code address any values that may exist in the source table (but have NOT been mapped -- for whatever reason -- in the LK table)?

By source table I assume you mean tbl_MMAC. I've posted 3 solutions over my previous two posts. In solution #1 I handled this by setting the default value of rslt to an empty string. In solution #2 it checks if the lookup was null and returns a default value of an empty string instead. In solution #3 it uses the Nz function to return an empty string instead of a null.

I realize now based on your excel file that the default value for BLS_ACCIDENT_TYPE_MMAC should have been a Null instead of the empty string. Can you study the code(s) to figure out the necessary adjustments?

[skydivetom]

Vlad -- I'll look into it... unfortunately, I can't post real data in the forum here. So, I would have to make up some stuff... I'll see what I can do.

kd2017 -- the VBA for the 1st field seems to work great. It doesn't work for the 2nd field given it's a number (vs. string). What code adjustments would have to be made for the 2nd field. 3rd field is string (like 1st field); however, I didn't have a lookup table for it. I created one but this VBA execute and nothing is converted yet. Still checking.

[kd2017]

kd2017 -- hmh, very interesting approach!!

How would the latter be put together for the 3 fields currently used (incl. the tier level for 1st and 2nd field)?

Cheers,
Tom

I wasn't considering your final insert query or tiers, I was just trying to eliminate the excel file step. The final most maintainable solution may be vba. I'll study your qry01_MMAC this afternoon.

[Gicu]

I didn't mean real data, but real "tables" with real values for the PK's, just replace any sensitive data with dummy values. And not too many records, just enough to capture the existing data diversity.

How do you build the lookup tables? What tells you that BLS_ACCIDENT_TYPE_MMAC=10 goes into INJ_INJURY_MECHANISM_TIER2_ID and what table is TARGET_PK=1 coming from?

Cheers,

[skydivetom]

Vlad -- I'll prepare some sample files.

Btw, per question "What tells you that BLS_ACCIDENT_TYPE_MMAC=10 goes into INJ_INJURY_MECHANISM_TIER2_ID". This is a one-time manual effort which can not be streamlined. Once the mapping has been established though, it should not change. Naturally, there may be a value change here or there but these are critical. Also, I will drop the field [TARGET_PK_FK] and move them into [TARGET_VALUE] which either will be numeric or string values. Before, I ended up creating a "string" version and also a "PK" version. Realized that the string version is no longer needed. But again, the single field [TARGET_VALUE] can hold string/number and **potentially* other data types. I will verify the latter though.

I'll try to post some additional sample tables later on. In the meanwhile, based on kd2017's recommendation, I have the following: It works for 1st and 3rd field (string) but not for 2nd field (number).

[Gicu]

Hi Tom,
Just another question, why not use the target field names in the master table (INJ_INJURY_MECHANISM_TIER2_ID instead of BLS_ACCIDENT_TYPE_MMAC_Tier_2)?
I guess what I'm saying the lookup tables do not match the target table (master).
Cheers,

[skydivetom]

Vlad -- I provided another example... hopefully this will clarify some information.