Would like to use fewer queries to get output

[skydivetom]

Experts:

I need some assistance with "streamlining" a query. Attached database includes the following objects:

1. Table "T01_Billets" -- contains eight (8) fields (e.g., [Acct_BA_Off], [Acct_OB_Off], etc.) where values are either 0 or 1.
2. Table "T01_StaffMembers" -- contains a Yes/No field
3. Nine (9) queries

Current process:
- The first eight queries (prefix "Q1_" through "Q8_") are individual queries which output a count of their associated field in [T01_Billets]
- Query "Q9_BilletsAuth_Onboard_TotalSum" then combines the output of the eight (8) queries into a single row/record and produces a "total sum" listed in "BA_Total" and "OB_Total"

Here's what I need some help with:


- Instead of using 8+1 queries, I'd like to reduce the number of required "input queries" (Q1-Q8).

My question: How can I generate the output from "Q9" using fewer queries? Is there a way to get the same results via a single query (or maybe with 1 or 2 helper/input queries vs. 8 helper/input queries)?

Thank you,
EEH

[skydivetom]

Never mind... I figured it out using one and only one query.

[orange]

Glad you found a solution.

[davegri]

Example-davegri-v01.zip
I had spent a bit of time on this before the OP declared his own solution.
However, since no solution is posted here's what I came up with.
It uses a temp table, a bit of VBA and one final query.
It requires a button or some method to call sub Looper.

[skydivetom]

Davegri:

Thank you for posting your proposed solution. I posted my "Aggregate" query which doesn't require any additional VBA.

However, I now need to further tweak the query to include a criteria from "T01_Staffmembers". That is, I'd like to modify the query and add a crteria where [T01_Staffmembers].[All_Onboard] = True.

As illustrated in the picture below, adding the [All_Onboard] field and setting the criteria results in an error. W/o that field, the query executes w/o any issues (i.e., doesn't require helper queries nor VBA).

My question: How can I include this field (value = true) w/o getting this error?

Thank you,
EEH

[davegri]

Move All_OnBoard field to T00_JunctionTable_OBS. Then you won't need T01_StaffMembers in the query.
I moved the field in my posted solution.

[skydivetom]

Davegri - this would be an issue in my actual database. I need the field "Onboard" to remain in T01_Staffmembers. Is there another way I can include it as an expression in the query?

[skydivetom]

Davegri - see attached JPG... this gives me the option to include the field and add the criteria. Thanks for chiming in.