Experts:
I need some help with *streamlining* up to 6 queries IOT conduct data analysis. Allow me to provide some background for the three tables first:
Table [ILLNESS]:
- Includes 20 records with values from "ILL_FIELD_01" through "ILL_FIELD_20"
Table [PERSON]:
- Includes 10 records with values from "PER_FIELD_01" through "PER_FIELD_10"
Table [01_tbl_ProductTables]:
- Contains a total of 36 records
- Includes three (3) source organizations (i.e., [SOURCE_FILE]): Alpha, Bravo, Charlie
- The fourth source = "EXTRA" is a dummy organization
- In field [AFSAS_FIELDNAMES], I reference the 20 ILLNESS-related and 10 PERSON-related fields.
- In field [FIELDNAME_STANDARDIZED], I make reference to legacy field names.
More info on [01_tbl_ProductTables] FOR ILLNESS-related records:
- There is a total of 24 ILLNESS-related records in [01_tbl_ProductTables]:
- In the case of organization = "Alpha", there is one record where [FIELDNAME_STANDARDIZED] = "Legacy_Field_1" is mapped to [AFSAS_FIELDNAME] = "ILL_FIELD_01"
- In the case of organization "Bravo, there are 3 records. Having similar mapping, legacy #1 is mapped to AFSAS #1, legacy #2 is mapped to AFSAS #2, and so forth.
- In the case of organization "Charlie, there are 4 records. Similar mapping between legacy and AFSAS applies for "Charlie".
- Finally, given that "Charlie" has up to 4 mapped fields, source file = "EXTRA" includes AFSAS_FIELDNAMES from "ILL_FIELD_05" through "ILL_FIELD_20".
More info on [01_tbl_ProductTables] FOR PERSON-related records:
- There is a total of 12 PERSON-related records in [01_tbl_ProductTables]:
- For each organization (Alpha to Charlie), there are 2 records mapped from legacy to AFSAS.
- And just like for ILLNESS, the "missing" records (up to 10 PERSON records) are covered by dummy organization "EXTRA".
Now to the queries:
- For each product (i.e., ILLNESS and PERSON) I currently have 3 queries:
- For example, for the ILLNESS product, I have "qry_ILLNESS_FieldsUtilized", "qry_ILLNESS_Gap_Input", and "qry_ILLNESS_Gap_Output"
- Same set of 3 queries for the PERSON product.
ILLNESS-related queries:
Info on query #1 "qry_ILLNESS_FieldsUtilized":
- Upon opening, I am prompted to enter a source. Let's say "Alpha".
- Now, remember the total # of ILLNESS-records in table [ILLNESS] = 20 records.
- In this case, the query returns 17 records. So, I'm missing information about 3 records (i.e. legacy fields not mapped to AFSAS).
Info on query #2 "qry_ILLNESS_Gap_Input":
- This serves as a "helper query"... let's skip the explanation for now. I'll get back to it.
Info on query #3 "qry_ILLNESS_Gap_Output":
- In query #1, we entered "Alpha" and it returned 17 out of 20 records.
- When opening ILLNESS' Q3 and enter "Alpha", it provides me the 3 missing records.
- Same applies when entering, e.g., "Bravo" in both query #1 and query #3. That is, Q1 returns 19 records while Q3 returns 1 record.
- For Charlie, however, I get 20 records Q1; thus, I get 0 records in Q3.
PERSON-related queries:
- Alpha returns 8 records on Q1 and 2 records on Q3... giving me the total of 10 PERSON records/fields.
- In this case, same record counts of Bravo and Charlie (8+2 = 10). The only difference (in each Q1/Q3 query) are the values of the existing/missing fieldnames.
Now, here's what I need some help with:
a. For my actual data set, I have 5 organizations (instead only 3).
b. More importantly though, I have 10 product tables (versus just 2 in this sample DB).
c. Thus, in my current setup I would have to create another set of 8 x 3 (i.e., 24) queries giving me a total of 30 queries IOT to analyze which legacy fields have been mapped and/or are missing.
d. Based on c), this appears to be very inefficient (e.g., criteria = ""ILLNESS" or "PERSON") are hard-coded in query #1.
e. Also, as you can see, query #3 uses the "helper query" (query #2).
My question:
How can the 2 sets of 3 queries each be re-structured so that I do NOT NEED static helper query #2 and ultimately end up with, e.g., only 3 queries (or even less)?
So, ultimately, I'd love to have a process (maybe via listbox in a form) where I pass the source (Alpha to Charlie) and product (ILLNESS and PERSON) into query criteria telling me which fields have been mapped / are not mapped.
Thank you for your help in advance.
Cheers,
Tom