Results 1 to 1 of 1
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Need to append records into different fields (based on values in lookup table)

    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
    Attached Thumbnails Attached Thumbnails Current_EnvisionedProcess.JPG  
    Attached Files Attached Files

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Rate Lookup based on values of 3 fields
    By tbcguytb in forum Access
    Replies: 11
    Last Post: 04-10-2020, 11:43 AM
  2. Replies: 3
    Last Post: 06-02-2017, 03:49 PM
  3. Replies: 3
    Last Post: 08-20-2015, 09:49 PM
  4. Replies: 4
    Last Post: 07-28-2015, 10:14 AM
  5. Replies: 5
    Last Post: 06-14-2012, 08:30 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums