Auto-generate queries via VBA (need extended capability)

[skydivetom]

B-e-a-u-t-i-f-u-l !!! Thank you for this update. Cheers!

[skydivetom]

Vlad:

Here's a follow-up to the data mapping issue. Based additional conversation w/ the team, there are two (2) scenarios that result in the data mapping issue.

**** SCENARIO #1 ************************

*Within the same source* (e.g., within "Organization Alpha"), there are multiple [FIELDNAME_STANDARDIZED] that are mapped to one (1) [AFSAS_FIELDNAME]. So, we have duplicate values in [AFSAS_FIELDNAME]. I will work on those and remap them to DISTINCT values in [AFSAS_FIELDNAME].

Example:
Both "LastName" and "FirstName" ([FIELDNAME_STANDARDIZED]) are mapped to "PERS_LASTNAME" ([AFSAS_FIELDNAME]).

Solution:
- Again, in this notional example, I will make sure that "LastName" ([FIELDNAME_STANDARDIZED]) is mapped to "PERS_LASTNAME" ([AFSAS_FIELDNAME]), and
- "FirstName" ([FIELDNAME_STANDARDIZED]) will be mapped to "PERS_FIRSTNAME" ([AFSAS_FIELDNAME])


**** SCENARIO #2 ************************

*Across different sources* (e.g., "Organization Alpha" AND "Organization Bravo"), there are multiple [FIELDNAME_STANDARDIZED] that are mapped to one (1) [AFSAS_FIELDNAME].

Example:
I may have "LastName_1" (from "Organization Alpha") and "LastName_2" (from "Organization Bravo") where both of them are mapped to "PERS_LASTNAME" ([AFSAS_FIELDNAME]).
In this case, I'm stuck with the M:1 mapping into "PERS_LASTNAME".

Potential Solution:
- I have added field [SOURCE_FILE] to table [01_tbl_ProductTables].

... I also have made the following changes:
- Changed the form's listbox property *Multi Select* from "Extended" to "None"
- Temporarily, changed property of the "Select All" & "Deselect All" to non-visible. Ultimately, these two buttons probaly can be deleted.


Here's what I need some help with:
a. Adjust the VBA (I believe line "Set rs = CurrentDb.OpenRecordset("SELECT *....") so that I can execute the product tables based on only a single source file (only "Alpha" OR only "Bravo").
b. Assuming I don't have any duplicate [AFSAS_FIELDNAME] based on scenario #1, the duplicate of scenario #2 should be ignored given that field [01_tbl_ProductTables].[SOURCE_FILE] would NOT include the duplicate [AFSAS_FIELDNAME].

My question:
How can the code be modified so that I only run the *mapped* records for, e.g., "Organization_Alpha" when selecting this value from the listbox? Naturally, when selecting any other value, I only execute the related fields in the product tables.

Food for thought... I my present Products table, I currently have 750 records mapped. Naturally, given the addition of the organization value, this record count may grow to 3,550 (since I have 5 different org values). Well, the number may be less given that not all 5 organizations have the same fields. Relatively speaking, 3700+ plus records in this table may not be bad though.

If you believe there's a better way of approach for addressing scenario #2, I'm open to your recommendations.

Thank you in advance for the continued help!!!

Cheers,
Tom

[Gicu]

Hi Tom,
I believe a much better way to deal with Scenario 2 is the solution I gave you in post #20. Because only 1 of the LastName_1 and LastName_2 will have data for any one record using LastName:max2([LastName_1],[LastName_2]) (or LastName:max2([LastName_3],max2([LastName_1],[LastName_2])) for 3 fields and so on) will give the correct value. So in your product table you replace the duplicate entries with one line:
FIELDS_STANDARDIZED ASFAS_FielNames
LastName (calculated in source query) PERS_LASTNAME

Once you setup the queries to deal with these they will stay in the db (in that version I modified the DeleteAllQueries sub to skip any that start with "00_"). No other changes to the latest version are required.

Cheers,
Vlad

[skydivetom]

Vlad -- I'm not 100% confident I'm following the example of the version included in post #20. Please see attached JPG which illustrates the constraint.

Am I missing something obvious? I don't have much flexibility on the AFSAS_FIELD mapping for scenario #2.

[Gicu]

Hi Tom, open the query 00_qry_Mater_PK in design view and you will see the calculated NORMALIZED_NAME:




As you see the query takes the maximum value between the two fields so the non-blank one will always be returned. So in your case if each operational group\source will have one LastName field populated (LastName1 will be populated for Alpha and will be null for Bravo, Charlie, Delta, etc.; LastName2 will be blank for Alpha, populated for Bravo and blank again for the rest....). Creating a calculated field combining these fields will result of having only the proper value for each source AS LONG AS THEY ARE MUTUALLY EXCLUSIVE (LastName1 cannot be populated for both Alpha and Bravo).

Hope this makes it a bit clearer to follow.

Cheers,
Vlad

[skydivetom]

Vlad:

Thank you for providing additional details. I can now better follow the logic. Unfortunately, I'm still not convinced the "normalized" version will work for me. Allow me to provide more details.

1. First, the two tables using "Demographics" and "Other" were created purely for demonstration purposes. My hope was to illustrate my original challenge using simple data analogy.
2. Reality is that my actual data fields are -- at least to some degree -- very much different from the example data... even though I do have "Lastname" and "Firstnames", etc.
3. For sake of argument, however, allow me to stay focused on the example data for another moment. I'll summarize in bullet format below:

More info on example data:
a. Once I ran the "string" version and open up "tblDemographics", I no longer see the field PERS_FIRSTNAME. Obviously that would be an issue.
b. Also, as indicated earlier, the "lastname" vs. "firstname" field should fall into "scenario #1"... that is, I will be able to push each of the two fields into a dedicated AFSAS_Fieldname.
c. Ultimately, I could haven chosen any other two fields (e.g., Age and Gender) and mapped both to let's say PERS_AGE. Naturally, pushing "Male" or "Female" into the Age field wouldn't make sense.
d. All that said, any field with a different fieldname (lastname vs. firstname; age vs. gender; email vs. phone, etc.) fall into the scenario #1 category. That is, as part of the analysis, I have to correct the mapping and identify the correct target mapping so that I'm not trying to push a firstname value into the lastname field (or gender value into the age field).

... I hope info a:d helped clarify the example data little bit more.

Now, please bare with me to further describe the scenario 2 data:
- Here we are dealing with "apples to apples"... which is a good thing, right.
- Data of any of the four organizations (Alpha through Delta) have a lastname field. All of these are mapped **correctly** into [PERS_LASTNAME]. This is what I meant by "constraint".
- However, organization "Alpha" may call that field, e.g., "Lastname" while organization "Bravo" may store last names in field, e.g. "NameLast".
- So, both fields [Lastname] and [NameLast] are mapped to [PERS_LASTNAME]. And, as you know, having two records with the same value in table [01_tbl_ProductTables].[AFSAS_FIELDNAME] throws the "duplicate error".

So, all that said, I'm still wondering if the proposed solution (post #32) would address that "Scenario #2" issue. With your permission, I further expand just a tad:
- I have learned (and tested) that I can have duplicate [AFSAS_FIELDNAMES] **as long** as they are being isued in different [PRODUCT_TABLE].
- So, if I had two records where [AFSAS_FIELDNAMES] = 'PERS_LASTNAME' but the [PRODUCT_TABLE] = "Demographics" for the 1st record and "Other" for the 2nd record, it's not an issue...

... well, I know you are fully aware of the latter.

But on that fact though, I though having an additional filter/criteria (i.e., SOURCE_FILE = Alpha OR Bravo OR Charlie, etc.) would then also allow me to have PERS_LASTNAME in the tbl_Demographics given that the SQL would NOT include the 2nd or 3rd or 4th LASTNAME given that I only selected "Alpha" from the listbox.

In conclusion, I tried the followng VBA code below but it does NOT work (in version "By Source.accdb" -- attached in post 32):

    Set rs = CurrentDb.OpenRecordset("SELECT * FROM 01_tbl_ProductTables WHERE AFSAS_MAPPED=True AND FIELDNAME_STANDARDIZED Is Not Null AND SOURCE_FILE='" & Forms!F01_MainMenu!lstSourceFile & "' Order BY PRODUCT_TABLE, AFSAS_FIELDNAME;", dbOpenSnapshot)

Is there any chance you might be able tweak the post 32 version so that I at least could see how it may work?

Thank you,
Tom

[skydivetom]

Vlad -- I previously responded your your answer in post #36. Based on that information, I continued to tweak the most recent version in hopes the SQL would allow me to have DUPS in AFSAS_FIELDNAME under the assumption I have a different org value.

I **somewhat** succeeded... see attached zip file. I now can have dup fieldnames within the same product table.

However, something is NOT working right. That is, only Organization Alpha has *new fields" (1:6). No matter which of the organizations I'm selecting all four org (Alpha through Delta) include the new fields that only "belong" to Alpha.

How should the code be tweaked to that I only get the products table based on the product lookup table.

Thoughts/recommendations?

P.S. I heard you recommendation about the normalized table loud and clear... I just don't think that approach will work for me. So, please bear w/ me on the modified approach. Thank you!

[Gicu]

Hi Tom,
With your approach you will increase the time needed to process the tables as you will need to merge the resultant tables into a final "product" table after you run each organizational group. While that can also be automated it just adds extra steps to an already complicated process.
Please review this file that I put together to reflect your scenario 2 from post #36, I even added a third field for Org Charlie and streamlined the VBA function to simply list the fields passed as an parameter array) - please review the queries in both design and datasheet view and compare to the corresponding Master tables. I think this would be the easiest approach to achieve your goal but please let me know if you have any issues with it and I will try to make your By_Source file work.


Cheers,
Vlad

[skydivetom]

Vlad:

First of all, I woud like to acknowledge your superb help. Nowadays, it is EXTREMELY rare to get support the way you've been demonstrating it. So, I am very, very grateful for your help.

//

I reviewed the latest DB (post 38). Unfortunately, I am still not convinced that it will solve my "scenario #2" fields. I think the difficulties lies in illustrating the challenge when using
the dummy fields (lastname, age, gender). I take full responsibility for not including a better example data set which may have mitigated some of these questions.

W/o trying to make an excuse, I just didn't realize (at the beginning of this post) where this solution would end up and that this issue would arise.

Anyhow, please review the attached JPG. I hope it will better illustrate the challenge w/ the fieldnames. Allow me to recap on the products table for the actula DB:

1. As of now, I have 156 DISTINCT values in [FIELDNAME_STANDARDIZED].
2. When I run a DISTINCT value count for field [AFSAS_FIELDNAME], I get a count of 79.
3. Currently, out of the 156 DISTINCT [FIELDNAME_STANDARDIZED], 37 have a DISTINCT mapping to [AFSAS_FIELDNAME].
4. However, there are 43 records which have duplicate values in [AFSAS_FIELDNAME]. As illustrated in the JPG, some of them are mapped up to 5 times.
5. Also, as noted in the JPG comments box, I plan to reduce the "43* records in "00_Duplicate_Error" by determining a distinct field (thus, I call them "scenario 1" fields).
6. Ultimately though, there is a 100% probability that at least a few fields (I don't that exact # yet) will have the same [AFSAS_FIELDNAME] *within* the same product (e.g., INJURY).
7. Thus, based on #6, these are the "trouble fields".
8. Based on #7, however, these trouble fields could be dealt with once the "organization reference" (Alpha through Delta) would be taken into account.

In summary, the **example** table [01_tbl_ProductsTables] in post #37 looks very much different. However, it mimics the dilemma of the trouble fields where, e.g., "PERS_LASTNAME" is used four (4) times. At the same time, when including the value of [SOURCE_FILE] into the VBA, then there are no repititions/dups IN [AFSAS_FIELDNAME].

So, at the present time, my goal is to merely include field [SOURCE_FILE] into the processing of the products table generation. If I can process all four example organization (Alpha through Delta), that would be great. However, if there is a requirement to generate the products table one-by-one (by organization), that would be fine too.

Is that doable?

Thank you,
Tom

[skydivetom]

Vlad -- this is a follow-up to my previous post #39 (recommend to review that one first).

//

I have attached a version that **appears** to be working. I also have included a spreadsheet for validation purposes. In my products tables, I removed some records in order to have a mix of fields for each organization. Still, there's some overlap between of fields across organizations.

Again, based on this small data set, it appears the modified version is working as envisioned. I made the code changes listed below. I'd welcome your thoughts/feedback if you foresee any issues w/ this process though.

Public Sub UpdateQueries()

    Set rs = CurrentDb.OpenRecordset("SELECT * FROM 01_tbl_ProductTables WHERE AFSAS_MAPPED=True AND FIELDNAME_STANDARDIZED Is Not Null AND SOURCE_FILE='" & Forms!F01_MainMenu!lstSourceFile & "' Order BY PRODUCT_TABLE, AFSAS_FIELDNAME;", dbOpenSnapshot)


Public Sub fnCreateFields(sTable As String)

    Set rs = CurrentDb.OpenRecordset("SELECT * FROM 01_tbl_ProductTables WHERE [01_tbl_ProductTables].AFSAS_MAPPED =True AND FIELDNAME_STANDARDIZED IS Null AND SOURCE_FILE='" & Forms!F01_MainMenu!lstSourceFile & "' AND PRODUCT_TABLE = '" & _
                sProductTable & "'Order BY PRODUCT_TABLE, AFSAS_FIELDNAME;", dbOpenSnapshot)

[Gicu]

Hi Tom,

Sorry but now I am really confused. The target product tables will only show you the structure and data for the last organizational group that you ran. How are you going to put them together (both fields and records)?

I don't understand how the solution I've been recommending is not working for the scenario 2. I think the focus should be on the Master tables to see some real time data, I don't mean the actual proprietary data but how it is aggregated in those tables.

Lets take IN_INJURY_MECHANISM_TIER1_ID. It shows being the target of three fields (BLS_ACCIDENT_TYPE_MMAC_Tier_1, BLS_ACCIDENT_TYPE_Tier_1 and EVENT_106_Tier_1). In the master tables are there any records that have data in MORE THAN ONE of these three fields? If yes you have a mapping problem because no matter what you do you will lose all but one, unless you start concatenating values which is bad. For that case the new application would have to be redesigned to either add individual fields for 1-to-1 mapping or add a linking table that would hold one record from each of the three fields and the foreign key of the master record.

But if there is only field for each of those fields that contains data for each record then my approach will work. Using
IN_INJURY_MECHANISM_TIER1_ID: imax([BLS_ACCIDENT_TYPE_MMAC_Tier_1],[BLS_ACCIDENT_TYPE_Tier_1],[EVENT_106_Tier_1]) will extract that not null value from the three fields.

So before we do any more tweaks I think we need to look at the data, could you please upload a sample db with the Master tables reflecting what you have in your actual data, like different field names for different organizations as I tried to do last time (again no proprietary info, the same type of sample or just random numbers)?
Cheers,
Vlad

[skydivetom]

Vlad:

I apologize for the delayed response... I was away from my desk yesterday. Allow me to response to your comments/questions:

V. "The target product tables will only show you the structure and data for the last organizational group that you ran. How are you going to put them together (both fields and records)?"
T. You are absolutely correct. Originally we offered our 'data loaders' the option to load all organizations (from legacy to target) either togehter or separatetely. Turns out though the data loaders
prefer/request to load each organization sequentially. That said, having the ability to only load one org at the time will NOT be an issue.

V. "I don't understand how the solution I've been recommending is not working for the scenario 2."
T. I attempted to use the 'normalized' version (post 20) and plug in the actual data set. The results were not satisfactory. I would have to re-run them to provide more specific information but I recall that initial glance included both missing & incorrrect values, etc.

V. "Lets take IN_INJURY_MECHANISM_TIER1_ID. It shows being the target of three fields (BLS_ACCIDENT_TYPE_MMAC_Tier_1, BLS_ACCIDENT_TYPE_Tier_1 and EVENT_106_Tier_1)."
T. Exactly! That's why I need the organization as another filter to by-pass this scenario. Here's why: Org_Alpha uses field [BLS_ACCIDENT_TYPE_MMAC_Tier_1]; Org_Bravo used [BLS_ACCIDENT_TYPE_Tier_1]; while Org_Charlie uses the 3rd field [EVENT_106_Tier_1].
Each of these 3 distinct fields have similar/related information... never mind the field labels. That is, I can't not speak to as to why, e.g., "Accident Type" = "Event 106".
Given their related information all of these 3 fields are mapped to the same field. Now, as you know, when running it w/ the org filter = "Alpha", I'm only inserting [BLS_ACCIDENT_TYPE_MMAC_Tier_1] into [INJ_INJURY_MECHANISM_TIER1_ID]. So, given that we'll load
the organizations separately/sequentially, it does no longer pose an issue.


I hope the latter provided some additional details on the process. Allow me to recap:

a. The DB attached in post #40 works as it prevents the *dup* issue (based on scenario #2).
b. However, given that we still have to remap fields based on scenario #1, I have NOT been able to run the full data set (for each org) since we're still working on new ProductTables table where there won't be any dups *within* the same organization.
c. So, while not fully tested, I have already realized this this new version will cause some other slight headaches. I will elaborate below in bullet format.

While post #40 works, it will result in the following issues:
- In any prior version, I can identify n number of "New fields" (e.g., PERS_NEW_FIELD1, PERS_NEW_FIELD2, etc.)
- For whatever product, any *new* field was/is appended to the products tables w/ the correct data type and if available specified default value (DV). This works like a charm!
- Now, w/ the latest DB (incl. the org filter to overcome to duplicate target fields based across product tables), I would have to include, e.g., [PERS_NEW_FIELD1] multiple time.
- That is, I will have to replicate the new field record for each organization IOT to include it regardless of the organization for which data is pulled.

My question:
Is there an easy way to, e.g., UNION the a) AFSAS_FIELDNAMES by product table AND by organization with b) any new fields which are independent from the organization?
So, right now, if I had 150 *new* fields and 5 organization, I would have to specify 750 records just for the new fields. Again, I'm just curious if there's a way to tweak the code of "NEW DB" so that I include all fields for, e.g., "Organization Alpha" + any new fields where value for [SOURCE_FILE] is empty/null?

Thank you,
Tom

[Gicu]

Hi Tom,
No worries, no need to apologize.

T. Exactly! That's why I need the organization as another filter to by-pass this scenario. Here's why: Org_Alpha uses field [BLS_ACCIDENT_TYPE_MMAC_Tier_1]; Org_Bravo used [BLS_ACCIDENT_TYPE_Tier_1]; while Org_Charlie uses the 3rd field [EVENT_106_Tier_1].
Each of these 3 distinct fields have similar/related information... never mind the field labels. That is, I can't not speak to as to why, e.g., "Accident Type" = "Event 106".
Given their related information all of these 3 fields are mapped to the same field. Now, as you know, when running it w/ the org filter = "Alpha", I'm only inserting [BLS_ACCIDENT_TYPE_MMAC_Tier_1] into [INJ_INJURY_MECHANISM_TIER1_ID]. So, given that we'll load
the organizations separately/sequentially, it does no longer pose an issue.

Because they are distinct the iMax([Field1Alpha],[Field2Bravo],...) approach will work OK. Version 20 had a small bug in the formula and I had a more convoluted nested max2() approach while the one in post # 36 uses the new iMax that simply accepts a comma-delimited list of fields.
With your latest approach when you run it for org Alpha the resultant Demographics table will have the Pers_LastName,Pers_FirstName and the new field PERS_NewField1. If after that you run it for org Charlie the same table will be missing Pers_LastName and have Pers_Age instead. So you see is not only the new fields being a problem, but each org have to be padded with all other fields from all the other organization in order for the tables to have to correct structure, not only the new fields.

To include the new fields for all organizations just replace the exiting sub with this updated one and put NEW in the Source_File for all new fields to be included:

Public Sub fnCreateFields(sTable As String)


    Dim sField As String, sDataType As String
    Dim rs As DAO.Recordset, sProductTable As String, sIndex As String, vDefault
    Dim db As DAO.Database, fld As DAO.Field
    
    On Error GoTo fnCreateFields_Err
    
    sProductTable = Replace(sTable, "tbl_", "")
    
    'Set rs = CurrentDb.OpenRecordset("SELECT * FROM 01_tbl_ProductTables WHERE [01_tbl_ProductTables].AFSAS_MAPPED =True AND FIELDNAME_STANDARDIZED IS Null AND SOURCE_FILE='" & Forms!F01_MainMenu!lstSourceFile & "' AND PRODUCT_TABLE = '" & _
                sProductTable & "'Order BY PRODUCT_TABLE, AFSAS_FIELDNAME;", dbOpenSnapshot)
    
        Set rs = CurrentDb.OpenRecordset("SELECT * FROM 01_tbl_ProductTables WHERE [01_tbl_ProductTables].AFSAS_MAPPED =True AND FIELDNAME_STANDARDIZED IS Null AND SOURCE_FILE='NEW' AND PRODUCT_TABLE = '" & _
                sProductTable & "'Order BY PRODUCT_TABLE, AFSAS_FIELDNAME;", dbOpenSnapshot)


    If rs.RecordCount = 0 Then Exit Sub
    Do Until rs.EOF
        sField = rs("AFSAS_FIELDNAME")
        sDataType = rs("NEW_FIELD_DATA_TYPE")
        vDefault = rs("NEW_FIELD_DEFAULT_VALUE")
        CurrentDb.Execute "ALTER TABLE [" & sTable & "] ADD COLUMN [" & sField & "] " & sDataType & ";" 'add field
        CurrentDb.TableDefs.Refresh
        If Not IsNull(vDefault) Then
            Set db = CurrentDb
            Set fld = db.TableDefs(sTable).Fields(sField)
            
            Select Case fld.Type
            Case 10, 12
                'Allows to set the default value for the field
                fld.DefaultValue = vDefault
                'Populate existing rows
                CurrentDb.Execute "UPDATE [" & sTable & "] SET [" & sField & "] = '" & vDefault & "';"
            Case 1, 3, 4, 5, 6, 7, 11, 16, 19, 20, 21
                'Allows to set the default value for the field
                fld.DefaultValue = vDefault
                 'Populate existing rows
                CurrentDb.Execute "UPDATE [" & sTable & "] SET [" & sField & "] = " & vDefault
            Case 8, 22, 23
                'Allows to set the default value for the field
                fld.DefaultValue = "#" & vDefault & "#"
                 'Populate existing rows
                CurrentDb.Execute "UPDATE [" & sTable & "] SET [" & sField & "] = #" & vDefault & "#;"
            End Select
            
        End If
        
    rs.MoveNext
    Loop
    '      MyText       TEXT(50),
    '      MyMemo       MEMO,
    '      MyByte       BYTE,
    '      MyInteger    INTEGER,
    '      MyLong       LONG,
    '      MyAutoNumber COUNTER,
    '      MySingle     SINGLE,
    '      MyDouble     DOUBLE,
    '      MyCurrency   CURRENCY,
    '      MyReplicaID  GUID,
    '      MyDateTime   DATETIME,
    '      MyYesNo      YESNO,
    '      MyOleObject  LONGBINARY,
    '      MyBinary BINARY(50)
    
    'reset recordset to include all fields
    
    'Set rs = CurrentDb.OpenRecordset("SELECT * FROM 01_tbl_ProductTables WHERE [01_tbl_ProductTables].AFSAS_MAPPED =True AND PRODUCT_TABLE = '" & _
                sProductTable & "'Order BY PRODUCT_TABLE, AFSAS_FIELDNAME;", dbOpenSnapshot)
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM 01_tbl_ProductTables WHERE [01_tbl_ProductTables].AFSAS_MAPPED =True AND FIELDNAME_STANDARDIZED IS Null AND SOURCE_FILE IN ('" & Forms!F01_MainMenu!lstSourceFile & "','NEW') AND PRODUCT_TABLE = '" & _
                sProductTable & "'Order BY PRODUCT_TABLE, AFSAS_FIELDNAME;", dbOpenSnapshot)


    
    If rs.RecordCount = 0 Then Exit Sub
    Do Until rs.EOF
        sField = rs("AFSAS_FIELDNAME")
        sIndex = Nz(rs("INDEX"), "NO")
        Select Case sIndex
        Case "UNIQUE"
            CurrentDb.Execute "CREATE UNIQUE INDEX " & sField & " ON [" & sTable & "]([" & sField & "]) "
        Case "YES"
            CurrentDb.Execute "CREATE INDEX " & sField & " ON [" & sTable & "]([" & sField & "]) "
        Case "PRIMARY"
            CurrentDb.Execute "CREATE UNIQUE INDEX PK_" & sTable & " ON [" & sTable & "]([" & sField & "])WITH PRIMARY"
        End Select
    rs.MoveNext
    Loop
    
    Set rs = Nothing
    Set fld = Nothing
    Set db = Nothing
fnCreateFields_Exit:
    Exit Sub
    
fnCreateFields_Err:
    MsgBox Err.Number & " " & Err.Description
    Resume Next


End Sub

I am including the updated sample with the above code. I still would strongly recommend you review the one from post #36 as I think it would be a much easier solution. Having the ability to run it for all organizations in one go should not have any impact on the data loaders abilities to analyze the loaded data one organization at the time, it is just a matter of filtering by the source_file field.

Cheers,

[skydivetom]

Vlad:

thank you for understanding and being flexible. I truly believe your arguments are valid... I simply just couldn't get the normalized version to work for me.

Ok, WRT to the new version from post #43. I love the idea that the *new* fields only need to be defined once but are being UNION'ed given org value = "NEW". Totally awesome!

I did a quick test to verify that all orgs (for each product 'demographics' and 'other') end up with the desired fields. Before doing so, I renamed the *new* fields and re-sorted the table.

Summary where SOURCE_FILE = 'NEW'
- 'Demographics' product has new fields: _01, _02, _03
- 'Other' product has new fields: _04, _05, _06, _07

Summary where SOURCE_FILE = 'Alpha'
- 'Demographics' has n existing fields
- 'Other' has n existing fields
- No 'new' fields where [FIELDNAME_STANDARDIZED] = null

Summary where SOURCE_FILE = 'Bravo'
- 'Demographics' has n existing fields
- 'Other' has n existing fields
- Has one new field within 'Demographics' where [FIELDNAME_STANDARDIZED] = null. Fieldname = PERS_NEW_FIELD_10

Summary where SOURCE_FILE = 'Charlie'
- 'Demographics' has n existing fields
- 'Other' has n existing fields
- No 'new' fields where [FIELDNAME_STANDARDIZED] = null

Summary where SOURCE_FILE = 'Delta'
- 'Demographics' has n existing fields
- 'Other' has n existing fields
- Has one new field within 'Other' where [FIELDNAME_STANDARDIZED] = null. Fieldname = PERS_NEW_FIELD_20

Summary where SOURCE_FILE = 'Echo'
- 'Demographics' has n existing fields
- 'Other' has n existing fields
- Has one new field within 'Demographics' where [FIELDNAME_STANDARDIZED] = null. Fieldname = PERS_NEW_FIELD_30
- Has one new field within 'Other' where [FIELDNAME_STANDARDIZED] = null. Fieldname = PERS_NEW_FIELD_40


Results when executing Org 'Alpha':
- Demographics: All existing fields + new fields (_01, _02, _03) are added to the query. Success!
- Other: All existing fields + new fields (_04, _05, _06, _07) are added to the query. Success!

Results when executing Org 'Bravo':
- Demographics: All existing fields + new fields (_01, _02, _03) are added to the query. It misses new field "_10".
- Other: All existing fields + new fields (_04, _05, _06, _07) are added to the query. Success!

Results when executing Org 'Charlie':
- Just like 'Alpha', all fields are added... Success!

Results when executing Org 'Delta':
- Just like 'Bravo', it is misses new field "_20" within 'Other' product.

Results when executing Org 'Echo':
- This one missed new fields "_30" and "_40" within 'Demographics' and 'Other' table, respectively.

**************************

Again, I love this new version... it provides sooo much more flexibility for adding the new fields where Org = "New". However, it does NOT include any new fields where Org <> "New".

Can these four new fields (_10, _20, _30, and _40) be integrated in the product table generation? Naturally, _10 only goes into "Bravo", while "_20" goes into "Delta", while "Echo" gets both "_30" and "_40".

See attached JPG where I highlighted the missing fields.

Cheers,
Tom

[Gicu]

Here you go Tom, this should do what you want with the new fields.
Cheers,
Vlad