Need Assistance with query (sub SELECT) to obtain correct denominator for % calculation

[skydivetom]

Hello:



I need assistance with streamlining a) helper query and b) subquery linked to helper query WHERE I currently don't calculate the correct *denominator* (IOT obtain current running percentages).

Please find attached both *sample* DB as well as a high-level spreadsheet that shall facilitate with understanding as to how the correct denominator should be derived.

First, some background on the DB:
T101_ORGANIZATION: includes single org value = "ABC"

T102_MODULE: contains 2 fields [MODULE] and [SUB_MODULE]
- ... where there are 3 different values {A1, A2, A1_A2}
- ... and the third value "A1_A2" indicates a record overlap between A1 & A2
- ... and this is where -- currently -- my denominator is NOT caculated correctly.
- For example, when I want to calculate the denominator for, e.g., "A1", I really need to pull the SUM of "A1" + "A1_A2".
- Similarly, when I want to calculate the denominator for, e.g., "A2", I really need to pull the SUM of "A2" + "A1_A2".

T201_RESEARCH: contains single field [RESEARCH_STATUS]

*Helper* query "Q01_METRICS_RESEARCH_STATUS":
- Currently, a GROUPBY query, incl. [ORGANIZATION], [MODULE], [SUB_MODULE], [RESEARCH_STATUS] and a dynamic COUNT.

Metrics Query "Q02_METRICS_RESEARCH_STATUS_ABC_Alpha_A1"
- I am using the helper query "Q01"
- See query design for "criteria" for [ORGANIZATION], [MODULE], [SUB_MODULE]
- Then, is this is where the problem lies, I'm using a sub-SELECT query to obtain the denominators.

TOTAL: (SELECT Sum(Q01_METRICS_RESEARCH_STATUS.Count) AS SumOfCOUNT FROM Q01_METRICS_RESEARCH_STATUS GROUP BY Q01_METRICS_RESEARCH_STATUS.ORGANIZATION, Q01_METRICS_RESEARCH_STATUS.MODULE, Q01_METRICS_RESEARCH_STATUS.SUB_MODULE HAVING (((Q01_METRICS_RESEARCH_STATUS.ORGANIZATION)="ABC") AND ((Q01_METRICS_RESEARCH_STATUS.MODULE)="Alpha") AND ((Q01_METRICS_RESEARCH_STATUS.SUB_MODULE)="A1"));)

At this point, I recommend to open the attached spreadsheet.
- As shown in cell C2, the value = 22.
- However, as indicated in the description for T102_MODULE (see above), I calculate incorrect denominators such as illustrated in the MS-Access queries:
- Q02's denominator [TOTAL] should be: = 58 ("A1" = 22 + "A1_A2" = 36)
- Q03's denominator [TOTAL] should be: = 59 ("A1" = 23 + "A1_A2" = 36)
- Q04's denominator [TOTAL] should be: = 31 ("A1" = 8 + "A1_A2" = 23)
- Q05's denominator [TOTAL] should be: = 36 ("A1" = 13 + "A1_A2" = 23)

By the way, I tried the following subquery criteria, but it didn't work:

"A1" Or (Q01_METRICS_RESEARCH_STATUS.SUB_MODULE)="A1_A2"));

Doing so results in the following error when executing the query.
"At most one record can be returned by this subquery."


My question:
- First, how does the sub-SELECT SQL "TOTAL: (SELECT Sum(Q01_METRICS_RESEARCH_STATUS.Count) AS SumOfCOUNT FROM ...." need to be modified to obtain the correct product/sum (aka denominator) for both, e.g., "A1 + A1_A2"?
- Second, if there's a more efficient way to reduce the number of these 5 queries (1 helper + 4 actual queries), would you pls offer some suggestions for cutting down on the # of queries and, e.g., outputting a summary query.

Much obliged,
EEH