Help with UNION?

[stu]

Hi, I'm trying to create a report that shows how many sales each member of
staff have process over a certain time period. The problem is that the data
has to come from two tables. When I try and join the tables together in the
formula using UNION ALL, each member of staff appears twice - once with a
count from one table and once with a count from the other. Is there any way
I can join them together without this happening? Many thanks in advance.

The SQL for one table is:

SELECT DISTINCTROW ProcessedBy, Count(ProcessedBy) AS CountOfProcessedBy
FROM table1
WHERE (((OrderDate)<=#6/30/2004# And (OrderDate)>=#4/1/2004#))
GROUP BY ProcessedBy;

The SQL for both tables is:

SELECT DISTINCTROW ProcessedBy, Count(ProcessedBy) AS CountOfProcessedBy
FROM table1
WHERE (((OrderDate)<=#6/30/2004# And (OrderDate)>=#4/1/2004#))
GROUP BY ProcessedBy
UNION ALL
SELECT DISTINCTROW ProcessedBy, Count(ProcessedBy) AS CountOfProcessedBy
FROM table2
WHERE (((OrderDate)<=#6/30/2004# And (OrderDate)>=#4/1/2004#))
GROUP BY ProcessedBy;

.... though this shows each staff member twice. Any ideas?

 

[GreySky]

Create a new query that uses your union query as its base, then Group By the
name, and Sum the Count. One way or another, the union query will wind up
being a subquery.

-- Grey