Experts:
I need some assistance with modifying an existing APPEND query where values will be appended into 3 different fields based on some criteria.
I have attached two (2) versions of my **SAMPLE** data base (i.e., "Current Version" & "Envisioned Version"). Per the illustration (JPG), I need the assistance with the file "Envisioned Version".
Btw, for testing/demo purposes only, I am purposely focusing on a single field for now.
--------------------------------
Background/process of sample database **Current Version**:
- Database includes 2 tables...
- [tbl_SourceData] has 150 records (with single field [Accident_Type]).
- [tbl_Master] is blank (0 zero at the present time).
- qryAppendRecords has source = [tbl_SourceData] and appends 150 **converted** records into [tbl_Master].
- The conversion from, e.g., "00" into "Objects" are defined in the module "mod_ValueTransformation".
This works great and does NOT need any modification!
--------------------------------
Now, here's where I need some help...
Background/process of sample database **Envisioned Version**:
- To some degree, the overall process remains the same as in "Current Version". That is, the query reads in the source data and appends records with their "translated" values into [tbl_Master]).
- HOWEVER, please note that this version includes one (1) additional table which outlines the criteria as to which field should be populated with any of the 150 values.
- Specifically, table [LookupTable] includes a field [TIER_LEVEL] containing values = "TIER1" / "TIER2" / "TIER3" for each of the up to 30 possible accident types.
- So, [tbl_Master] now includes three (3) fields: [ACCIDENT_TYPE_TIER_1]; [ACCIDENT_TYPE_TIER_2]; [ACCIDENT_TYPE_TIER_3].
Envisioned process:
- Just like before, query "AppendQuery" appends the *converted* values BUT inserts them into their respective fields (Tier_1, Tier_2, Tier_3) given the information in [LookupTable].[TIER_LEVEL].
Does anyone have a recommendation how to modify the append query so that the converted values are appended into the designated fields?
Cheers,
Tom