SQL count summation query across multiple sets of Access tables

Question

I have a MS Access query (created with SQL) that counts the occurrences of non-NULL values in a date column in an equipment status table. The table contains all of the equipment for a particular manufacturing plant. The query also counts the total equipment, with our without NULL values. There are a total of 12 different plants, each with its own identical set of Access tables. I need to create a consolidated SQL query that creates a summation of each of the counts into a master count for all of the plants.

The structure of the status table, named '_review_status' is:

equip_number, text
review_a_analysis, date
review_b_analysis, date 
review_c_analysis, date
review_d_analysis, date
review_e_analysis, date
review_f_analysis, date
review_g_analysis, date

The results of the working query (on one table) look like this.

a   b   c   d   e   f   g   equip_count
17  31  0   94  13  12  44  1249

The new results should look exactly like the above, except that the number will all be larger because the query is looking at all 12 sets of tables.

Here is the working one-table query:

SELECT 
Count(dept1_review_status.review_a_analysis) AS a, 
Count(dept1_review_status.review_b_analysis) AS b, 
Count(dept1_review_status.review_c_analysis) AS c, 
Count(dept1_review_status.review_d_analysis) AS d, 
Count(dept1_review_status.review_e_analysis) AS e, 
Count(dept1_review_status.review_f_analysis) AS f, 
Count(dept1_review_status.review_g_analysis) AS g,
Count(dept1_equipment.dept1_equip_number) AS equip_count
FROM dept1_equipment 
LEFT JOIN dept1_review_status 
ON dept1_equipment.dept1_equip_number = 
dept1_review_status.dept1_equip_number;

The join on the dept1_equipment table is used to get the full count of all of the equipment in each department.

Many thanks.

Bob

Got great help form @sagi on a similar SQL challenge last week. In case his solution is helpful in navigating this one, see [link]stackoverflow.com/questions/34836370/… for details on that solution. — bcrimmins

Parfait Parfait · Accepted Answer · 2016-01-20T04:50:47

Consider running a union query of all 12 group by aggregate queries and then run another final aggregate query using the saved union query:

UNION

SELECT 
   Count(dept1_review_status.review_a_analysis) AS a, 
   Count(dept1_review_status.review_b_analysis) AS b, 
   Count(dept1_review_status.review_c_analysis) AS c, 
   Count(dept1_review_status.review_d_analysis) AS d, 
   Count(dept1_review_status.review_e_analysis) AS e, 
   Count(dept1_review_status.review_f_analysis) AS f, 
   Count(dept1_review_status.review_g_analysis) AS g,
   Count(dept1_equipment.dept1_equip_number) AS equip_count
FROM dept1_equipment LEFT JOIN dept1_review_status 
ON dept1_equipment.dept1_equip_number = dept1_review_status.dept1_equip_number;

UNION ALL

SELECT 
   Count(dept2_review_status.review_a_analysis) AS a, 
   Count(dept2_review_status.review_b_analysis) AS b, 
   Count(dept2_review_status.review_c_analysis) AS c, 
   Count(dept2_review_status.review_d_analysis) AS d, 
   Count(dept2_review_status.review_e_analysis) AS e, 
   Count(dept2_review_status.review_f_analysis) AS f, 
   Count(dept2_review_status.review_g_analysis) AS g,
   Count(dept2_equipment.dept2_equip_number) AS equip_count
FROM dept2_equipment LEFT JOIN dept2_review_status 
ON dept2_equipment.dept2_equip_number = dept2_review_status.dept2_equip_number;

UNION ALL

SELECT 
   Count(dept3_review_status.review_a_analysis) AS a, 
   Count(dept3_review_status.review_b_analysis) AS b, 
   Count(dept3_review_status.review_c_analysis) AS c, 
   Count(dept3_review_status.review_d_analysis) AS d, 
   Count(dept3_review_status.review_e_analysis) AS e, 
   Count(dept3_review_status.review_f_analysis) AS f, 
   Count(dept3_review_status.review_g_analysis) AS g,
   Count(dept3_equipment.dept3_equip_number) AS equip_count
FROM dept3_equipment LEFT JOIN dept3_review_status 
ON dept3_equipment.dept3_equip_number = dept3_review_status.dept3_equip_number;

...other 9 tables...

FINAL

SELECT Sum(unionqry.a), 
       Sum(unionqry.b), 
       Sum(unionqry.c), 
       Sum(unionqry.d), 
       Sum(unionqry.e), 
       Sum(unionqry.f), 
       Sum(unionqry.g), 
       Sum(unionqry.h), 
       Sum(unionqry.equip_count)
FROM unionqry

Alternatively, you can incorporate all in one query if MS Access allows depending on complexity:

SELECT Sum(unionqry.a), 
       Sum(unionqry.b), 
       Sum(unionqry.c), 
       Sum(unionqry.d), 
       Sum(unionqry.e), 
       Sum(unionqry.f), 
       Sum(unionqry.g), 
       Sum(unionqry.h), 
       Sum(unionqry.equip_count)
FROM (...above union query)... As unionqry

SQL count summation query across multiple sets of Access tables

1 Answers