select query problem

[dhx10000]

I don't think my previous post is possible, so to get around it, I would like to know if this is possibly. I have multiple tables that have the same column names. I need to go to each table, count specific values in that column, sum these values and update a separate table with the values. Here's an example:

For table1:

Role
-----
EUM
EUM
ETM
DIR

For table2:

Role
______
ETM
ETM
DIR
EUM

For table3:
___________
DIR
EUM
EUM

Here is what I need to show in the NewTable:

EUM
____
5

ETM
_____
2

DIR
____
3

Thanks in advance

 

[Jon K]

You can do it with a Union query followed by a Totals query.

qryOne:-
SELECT Role
FROM Table1
UNION ALL
SELECT Role
FROM Table2
UNION ALL
SELECT Role
FROM Table3;

qryTwo:-
SELECT Role, Count(Role) AS [Count]
FROM qryOne
GROUP BY Role;


Run the second query, which will return the results in this format:-

Role	Count
DIR	3
ETM	3
EUM	5

.