Aggregate SQL query across multiple tables
<p>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:</p>

<pre><code>SELECT dept1.actions.resource, dept1.action.status, Count(*) AS status_count
FROM dept1.action
GROUP BY dept1.action.status, dept1.action.resource;
</code></pre>

<p>Each of the tables looks like this:</p>

<pre><code>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)
</code></pre>

<p>The query results look like this:</p>

<pre><code>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
</code></pre>

<p>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.</p>

<p>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.</p>

<p>Thank you for any help you can provide.</p>