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

    Need help with Append Query

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

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Your envisioned design goes against proper db normalization, why not just two fields in tblMaster: AccidentType and AccidentTierLevel? Then it is just a matter of having the two fields in the append query.

    Otherwise if you want to keep your proposed design you need to use VBA to dynamically switch the SQL statement or use three append queries, one fo reach tier.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Vlad - I hear you... there are certain constraints which prevent me doing so.

    In the meanwhile, I think I have come up w/ a work-around. See attached JPG.
    Attached Thumbnails Attached Thumbnails WorkAround.JPG  

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You can store them in a normalized table but present them like you want using a simple crosstab query. Otherwise you would need three queries like the one you show (or more if in the future you add new tiers).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    That makes sense, Vlad... 'appreciate the feedback/recommendation.

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

Similar Threads

  1. Replies: 4
    Last Post: 02-03-2021, 08:32 AM
  2. Replies: 19
    Last Post: 10-03-2017, 09:10 AM
  3. Access can't append all the records in the append query
    By fluffyvampirekitten in forum Access
    Replies: 2
    Last Post: 08-27-2015, 01:53 AM
  4. Replies: 1
    Last Post: 10-06-2011, 08:37 AM
  5. Replies: 7
    Last Post: 07-21-2011, 01:01 PM

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