Multiple Query Report- Really Need HELP!

  • Thread starter Thread starter Charess
  • Start date Start date
C

Charess

I have 3 queries

1.) Count of Employee ID by Department - table 1

2.) Count of Employee ID by Category - Table 3

3.) Count of Employee ID by Final Action Table 3

These three queries all have a count on the employee id. I would like to put
them all on 1 report instead of three separate reports. I have done some
researching, but not found comprehensive instructions on how to do a union
query. So far I've been able to combine the employee id numbers, but its no
help since dept is in one table and category & final action are in
another...am I missing something?

One thing to note is that Table 2 pulls from Table 1 so it is not needed
except to filter specific selected employees. Table 1 is a database of all
employees.
 
Try this --
SELECT "Department" AS Data_Item, [Department], Count([Employee ID]) AS Tally
FROM [Table 1]
GROUP BY "Department", [Department]
UNION ALL SELECT "Category" AS Data_Item, [Category], Count([Employee ID])
AS Tally
FROM [Table 3]
GROUP BY "Category", [Category]
UNION ALL SELECT "Final Action" AS Data_Item, [Final Action],
Count([Employee ID]) AS Tally
FROM [Table 3]
GROUP BY "Final Action", [Final Action];
 
I have 3 queries

1.) Count of Employee ID by Department - table 1

2.) Count of Employee ID by Category - Table 3

3.) Count of Employee ID by Final Action Table 3

These three queries all have a count on the employee id. I would like to put
them all on 1 report instead of three separate reports. I have done some
researching, but not found comprehensive instructions on how to do a union
query. So far I've been able to combine the employee id numbers, but its no
help since dept is in one table and category & final action are in
another...am I missing something?

One thing to note is that Table 2 pulls from Table 1 so it is not needed
except to filter specific selected employees. Table 1 is a database of all
employees.
Click to expand...

A UNION query is pretty simple: you need two (or three, or more) queries which
return the same number of fields of matching datatypes. Assuming that your
three count queries consist of a text field (Department, Category, Final
Action respectively) and a number, you can open the SQL window and type in

SELECT [Department] AS Fieldname, CountOfEmployeeID FROM qryDept
UNION ALL
SELECT [Category], CountOfEmployeeID FROM qryCat
UNION ALL
SELECT [Final Action], CountOfEmployeeID FROM qryAct

You can add other fields (the fieldnames don't matter) just so they pair up:
number with number, date with date, text with text.

Alternatively you can put three Subreports on one Report.
 
Back
Top