Aggregate SQL query across multiple tables

Question

I'm using MS Access 2010. The database has a set of 12 identical equipment tables, one for each of 12 different departments in a company. The table tracks actions that need to be taken by folks (resources) who are responsible for the equipment. I have a simple query that counts the number of resources that have various statuses. It looks as follows:

SELECT dept1.actions.resource, dept1.action.status, Count(*) AS status_count
FROM dept1.action
GROUP BY dept1.action.status, dept1.action.resource;

Each of the tables looks like this:

equip_id, text
resource, number (id of the resource who is responsible for the equipment)
status, number (id of the status of the action that the resource needs to do)

The query results look like this:

resource  status  status_count
1         1       63
2         1       79
5         1       16
6         1       3
0         3       1
1         3       1180
2         3       64
3         3       61
5         3       1
6         3       2
7         3       12
0         4       4

For example, the first row shows that resource 1 has 63 pieces of equipment that are of status 1. The second row shows that resource 2 has 79 pieces of equipment of status 1... and so on.

What I need is an aggregate query that provides company-level totals for all of the resource and status combinations, i.e., the exact same result table, except that the status_count column will have significantly larger numbers.

Thank you for any help you can provide.

This is a difficult query in MS Access. You should combine the 12 tables into one. In general, having multiple tables with the same structure indicates a problem with database design. — Gordon Linoff

sagi sagi · Accepted Answer · 2016-01-17T08:23:41

Basically, you can select the count for each dep table in your database , Union them all and then sum them.

SELECT resource,status,sum(status_count) as status_company_count from(
SELECT dept1.actions.resource, dept1.action.status, Count(*) AS status_count
FROM dept1.action
GROUP BY dept1.action.status, dept1.action.resource
UNION
SELECT dept2.actions.resource, dept2.action.status, Count(*) AS status_count
FROM dept2.action
GROUP BY dept2.action.status, dept2.action.resource
UNION
SELECT dept3.actions.resource, dept3.action.status, Count(*) AS status_count
FROM dept3.action
GROUP BY dept3.action.status, dept3.action.resource
.....)
group by resource,status;

Aggregate SQL query across multiple tables

2 Answers