Prev: New Blank Query Shortcut
Next: Checking for Overlapping Events in access 2007
From: Charess on 13 Jan 2010 18:36
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.
From: KARL DEWEY on 13 Jan 2010 19:20
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];

--
Build a little, test a little.


"Charess" wrote:

> 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.
From: John W. Vinson on 13 Jan 2010 19:39
On Wed, 13 Jan 2010 15:36:01 -0800, Charess
<Charess(a)discussions.microsoft.com> wrote:

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

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

John W. Vinson [MVP]
 | 
Pages: 1
Prev: New Blank Query Shortcut
Next: Checking for Overlapping Events in access 2007