Page 2 of 2 FirstFirst 12
Results 16 to 20 of 20
  1. #16
    skydivetom is offline Expert
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    779
    Vlad:

    Good morning -- thank you for the follow-up... so, now I'm a bit confused now. Allow me to recap:

    1. The version from ~ a week ago, resulted in numerous VBA errors (incl. -- if I recall correctly -- a 3371 error).
    2. Based on the mapping at that point, I considered them "scenario 1" and "scenario 2" errors.


    2a. Scenario 1 error: There were duplicate AFSAS fieldnames within the *same product* AND *same source (organization)*
    2b. Scenario 2 error: There were duplicate AFSAS fieldnames within the *same product* but in different sources/organizations.
    3. Per 2b., the scenario 2 errors were fixed first by including the SOURCE_FILE as an additional criteria.
    4. Next, any dups due to scenario 1 were *temporarily* removed (for testing only). At that point, the code executed and all products were created as expected.
    5. It then took me some time to review all mapping issues due to scenario 1 errors. Once remapped, all previously removed/deleted records were added again. At that point, the code executed and all products were created.
    5b. See attached JPG reflecting that there are no dups in the project_tables regardless of scenario 1 or scenario 2.

    All that said, prior to opening the yesterday's new post, the VBA executed and all products were created as expected. So, this is why I'm a bit confused now. The introduction of the new code now prevents the same outcome. Is it possible that something (e.g., including "source" to prevent scenario 2 errors) was left out in the added code? Otherwise, why was it working (w/ the same mapping) yesterday but it is no longer working now?

    What am I overlooking in this scenario?

    Thank you,
    Tom
    Attached Thumbnails Attached Thumbnails SQL Code.JPG  

  2. #17
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,487
    Hi Tom,
    Can you please paste this SQL (the first error in the error file) into the query editor and review it? In all cases it seems like there are multiple legacy fields being mapped to one alias field:

    SELECT [00_tbl_Master_PK].SOURCE_FILE, CASE_NUMBER, [BLS_BODY_PARTID_MMAC_Tier_1] as INJ_BODY_PART_TIER1_ID,[BLS_BODY_PARTID_Tier_1] as INJ_BODY_PART_TIER1_ID,[BLS_BODY_PARTID_MMAC_Tier_2] as INJ_BODY_PART_TIER2_ID,[BLS_BODY_PARTID_Tier_2] as INJ_BODY_PART_TIER2_ID,[INJURY_DATE] as INJ_ENTRY_DATE,[BLS_ACCIDENT_TYPE_MMAC_Tier_1] as INJ_INJURY_MECHANISM_TIER1_ID,[BLS_ACCIDENT_TYPE_Tier_1] as INJ_INJURY_MECHANISM_TIER1_ID,[BLS_ACCIDENT_TYPE_MMAC_Tier_2] as INJ_INJURY_MECHANISM_TIER2_ID,[BLS_ACCIDENT_TYPE_Tier_2] as INJ_INJURY_MECHANISM_TIER2_ID,[BLS_ACCIDENT_TYPE_MMAC_Tier_3] as INJ_INJURY_MECHANISM_TIER3_ID,[BLS_ACCIDENT_TYPE_Tier_3] as INJ_INJURY_MECHANISM_TIER3_ID,[BLS_INJURY_TYPE_Tier_1] as INJ_INJURY_TYPE_TIER1_ID,[BLS_INJURY_TYPE_Tier_2] as INJ_INJURY_TYPE_TIER2_ID,[BODY_SIDE] as INJ_SIDE_OF_BODY FROM [00_tbl_Master_PK] WHERE [SOURCE_FILE] IN ('MMAC')

    If you wish you could send me just the products table to my email in my website (no actual data, just the field names) so I could try to understand what is happening.

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

  3. #18
    skydivetom is offline Expert
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    779
    Vlad -- thank you... just emailed you the tables (two versions... both of them execute w/ issues in yesterday's Product Generator tool).

  4. #19
    skydivetom is offline Expert
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    779
    Vlad -- your solution to this problem is far better (and more elegant) than I could have ever envisioned it. That is, it completely mitigates the need for the 3-step query analysis.

    Thank you again!

    Tom

  5. #20
    skydivetom is offline Expert
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    779
    Vlad -- good morning...

    I posted a new (but related) post @ https://www.accessforums.net/showthr...283#post478283

    I'd welcome any suggestions you may have. Thank you!

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Simple Data Analysis
    By dbuckmas in forum Access
    Replies: 6
    Last Post: 01-18-2018, 01:42 PM
  2. Inspection Record and Data Analysis Database
    By cap.zadi in forum Access
    Replies: 4
    Last Post: 06-17-2017, 03:50 AM
  3. Data Analysis Direction
    By canyon289 in forum Access
    Replies: 6
    Last Post: 03-05-2012, 11:51 PM
  4. Table Data Analysis
    By bdf48 in forum Programming
    Replies: 1
    Last Post: 12-01-2011, 02:21 PM
  5. Need Help with Queries-Trade Analysis
    By nybanshee in forum Queries
    Replies: 0
    Last Post: 03-08-2008, 11:50 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 - Senior Forums