Challenging query with execptions/conditions

[skydivetom]

Experts:



I need some assistance with either a) tweaking the existing queries or b) re-creating a query or n queries in order to get to the desired output.

Allow me to recap on the table [00_tbl_SourceFields] first. Also, to simplify the process

Background on table (and sample records):
=============================
a. This table is only includes a subset of my actual fields. For the purpose of this thread, the included fields should be sufficient though.
b. Field [SourceMerged] includes 5 organization values (Org_Alpha through Org_Echo).
c. Field [FieldnameStandardized] has a total of 9 DISTINCT values; however, I have 20 records due to duplicates across the 5 organizations. That's ok!
d. Field [MigrateField] contains Boolean values (true/false). In this case, 17 out of 20 records = true.
e. Fields [MultiTiered_Tier1]; [MultiTiered_Tier2]; [MultiTiered_Tier3] indicate tier level.

Note on Tiers 1 - 3:
- Per e. not all field values (e.g., "BADGE_NUMBER") are tiered on levels 1 through 3.
- Some fields (e.g., "INJURY_TYPE") have only tiers 1 and 2 checked.
- Other fields (e.g., "ACCIDENT_TYPE") is/are tiered on all 3 levels.

Current queries:
===========
- This sample DB includes 4 queries.
- "qry_Step1_1" generates initial results and serves as a "helper" query for "qry_Step_2"... which then serves as helper query for step 3 and so forth.
- "qry_Step_4" is my final view. And this is where I currently have a flaw that requires either "tweaking" (in subsequent helper queries).

My goal:
======
1. Generate an output (e.g., in "qry_Step_4") which shows me all of fieldnames that are utilized by each organization.
2. If a field is NOT tiered (e.g., "BADGE_NUMBER"), then I simply want to list the [FieldnameStandardized] and its associated source (Org_Alpha through Org_Echo).
3. However, if a field is tiered (e.g., "ACCIDENT_TYPE"), then I want to show [FieldnameStandardized]&"_Tier_n" and (in 2nd column) the organization/source.
4. At first glance, it appears that "qry_Step_4" does do exactly that... although it may not be very efficient (it takes 4 steps but the results seem promising).

Current flaw:
=========
5. When opening "qry_Step_4", the first record = "ACCIDENT_TYPE" (for Org_Echo) is wrong (in the query at least). This is due to record #4 where "OrgEcho" has no TRUE values in [MultiTiered_Tier1] through [MultiTiered_Tier3].
6. So while the source data (table) is correct, I somehow want to overwrite the results (via IIF statements) so "OrgEcho" value of "ACCIDENT_TYPE" should be displayed as "ACCIDENT_TYPE_TIER_1".
7. Per #6 please keep in mind I do NOT want to increase the number of query results by an additional 2 records (e.g., 33 vs. 31) by also including ""ACCIDENT_TYPE_TIER_2" and/or ""ACCIDENT_TYPE_TIER_3" for "Org_Echo".
That is, given that Echo is NOT tiered, I want the generic fieldname only be changed to "Tier_1" (assuming same fields are tiered by at least one other organization).

All that said, here's the final key take away. As I see it, the only incorrect record is the 1st value ("ACCIDENT_TYPE" | "Org_Echo"). It should automatically be converted to ("ACCIDENT_TYPE_Tier_1" | "Org_Echo").
All else (i.e, record count) appears to be correct.
Finally, if there's a way to compress the number of helper queries/steps from 4 to something smaller, that would be even greater.

I apologize if the information above is somewhat confusing. This legacy conversion process is very delicate and I tried to explain it with generic field and values IOT get the point across.

Cheers,
Tom

[Gicu]

Hi Tom,
Please have a look at the attached file and let me know if it works as expected. Note that I created a new table to hold the excluded fields as it would be easier to maintain the list in the future instead of editing the criteria in the query designer.
Cheers,

[skydivetom]

Vlad -- thank you for providing this solution. I was away from my desk so I didn't get to see it until a moment ago.

As always, your solution is sooo elegant and the results are perfect! Thank you so much.

Cheers,
Tom

P.S. This will help me w/ the remapping of the "scenario 1" products table. You know what I'm referring to. Awesome!!

[Gicu]

Tom, glad to hear it works for you. While obviously not knowing the whole story here I would strongly suggest you review my suggestions on post # 20 of your other thread (https://www.accessforums.net/showthr...t=83691&page=2). You might be missing the opportunity here to normalize your db structure so when Tier4 comes along you are are ready for it...
Cheers,
Vlad