Experts:
I need some help with modifying an existing process/query where I'm appending records to a MASTER table. I have attached two (2) versions of a **SAMPLE** data base. For testing/demo purposes only, I am purposely focusing on a single field to keep the example content at a minimum.
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".
That's really it for sample DB "Current Version".
--------------------------------
Now, to sample DB **Envisioned Version**:
- To some degree, the overall process remains the same (i.e., read in the source data and append records with their "translated" values into [tbl_Master]).
- However, please note that this version includes one (1) additional table.
- Specifically, table [LookupTable] includes a field [TIER_LEVEL] containing values = "TIER1" / "TIER2" / "TIER3" for each of the up to 30 possible accident types.
- Next, I merely copied table [tbl_Master] and named it [tbl_Master_Envisioned].
- The latter table now includes three (3) fields: [ACCIDENT_TYPE_TIER_1]; [ACCIDENT_TYPE_TIER_2]; [ACCIDENT_TYPE_TIER_3]
Now, here's what I need some help with:
- Modify the query "AppendQuery_Envisioned" and append the *converted* values (based on 'mod_ValueTransformation') BUT NOW insert the converted values into the respective fields (Tier_1, Tier_2, Tier_3) based on table [LookupTable].
- Please see the attached JPG which illustrated the change from "current" to "envisioned" process.
Thank you for your help in advance!
Cheers,
Tom