You can in fact do this with a single query. This sort of 'conditional
aggregation' is done by summing the return value of an expression which
returns either 1 or 0. Summing all the rows which return 1 is of course the
same as counting them. You can use the IIF function to return a 1 or 0, so
in effect you can conditionally count rows in different columns. The query
would be like this:
PARAMETERS
[Enter start date:] DATETIME,
[Enter end date:] DATETIME;
SELECT [Employee Name],
SUM(IIF([New Hires by Date] BETWEEN [Enter start date:] AND [Enter end
date:],1,0))
AS [Number of Hires],
SUM(IIF([Promotion by Date] BETWEEN [Enter start date:] AND [Enter end
date:],1,0))
AS [Number of Promotions],
SUM(IIF([Resignations by Date] BETWEEN [Enter start date:] AND [Enter end
date:],1,0))
AS [Number of Resignations]
FROM [Your Table]
GROUP BY [Employee Name];
Make sure the table and column names exactly match yours of course. To
create the query, open the query designer, switch to SQL view and paste in
the whole of the above. Then change the table and /or column and or column
names in the SQL statement as necessary. Note that with date/time parameters
it is always a good idea to declare these with a PARAMETERS clause at the
start of the query as above. Otherwise a date value entered at the parameter
prompt in short date format could be misinterpreted as an arithmetical
expression rather than a date and give the wrong results
When you open the query you'll be prompted for the start and end dates for
the range. The query should then return the number of rows in each column
per employee where there are date values within the range.
The above query is grouped by employee name as that's the only column you
mention, but as names can be duplicated (I once worked with two Maggie
Taylors) you should distinguish between possible duplicates by giving each
employee a unique EmployeeID. If you don't already have one simply add an
autonumber column of this name. Then change the first line of the SELECT
clause to:
SELECT [EmployeeID], [Employee Name],
and the GROUP BY clause to:
GROUP BY [Employee Name], [EmployeeID];
Turning to a more fundamental design point, however, having separate columns
for each 'event type' is not good design. Its what's known as 'encoding data
as column headings'. In a relational database data should be stored as
values at column positions in rows in tables and in no other way; its called
the 'information principle'. What you should do is 'decompose' the table
into three related tables, e.g. Employees, Events, and EventTypes (you can
use whatever names you wish of course but try and reflect what the table
represents).
The Employees table will have one line per employee, with EmployeeID as its
primary key, along with columns for the employee's names etc.
The EventTypes table will have just one column, EventType with values such
as 'New Hire', 'Promotion' and 'Resignation'. This column will be the
primary key.
There is a many-to-many relationship type between Employees and EventTypes
in that each employee might undergo one or more event types, and each event
type applies to one or more employees. It’s the Events table which models
this relationship type, which it does by having tow foreign key columns,
EmployeeID (not an autonumber in this case) and EventType, so the table
resolves the many-to-many relationship type into two one-to-many relationship
types. It will also have an EventDate column.
In the relationships between Employees and Events, and between EventTypes
and Events referential integrity should be enforced. In the relationship
between EventTypes and Events 'cascade updates' should also be enforced.
This is because in this case the text name of the event type is being used as
a 'natural' key rather than a 'surrogate' numeric key as with EmployeeID.
Enforcing 'cascade updates' means that if the name of an event type in the
EventTypes table is changed it will automatically be changed in all matching
rows in Events.
To count the values within a date range per column with this properly
'normalized' design you can join the Employees and Events tables in a query
(you don't need to include the EventTypes table for this), restrict the
results to the date range in. In this case you'd group the query by Employee
and then by EventType, and COUNT(*) to count the rows, so the query would be:
PARAMETERS
[Enter start date:] DATETIME,
[Enter end date:] DATETIME;
SELECT [EmployeeID], [Employee Name], [EventType],
COUNT(*) AS [Total]
FROM [Employees] INNER JOIN [Events]
ON [Employees].[EmployeeID] = [Events].[EmployeeID]
WHERE [EventDate] BETWEEN [Enter start date:] AND [Enter end date:]
GROUP BY [Employee Name], [EmployeeID], [EventType];
Ken Sheridan
Stafford, England
NDNobbs said:
We did it! Thanks so much!
--
NDNobbs
Danny Lesandrini said:
Great news ... you're so close.
Add the 3 totals queries to the main query and link to tblEmployee on EmployeeID.
Make sure it's a Left Join, with the little arrow end of the join pointing to the queries,
not to the main table. If the queries work alone, they will work in this configuration.
--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com
NDNobbs said:
Am I correct in that all I have to do to make it a Group By query is select
'totals' on the toolbar and I get the drop down box choices? Or is there more
to it?
I've set up the queries and they run beautifully on their own, but I cannot
get any of the data from the subqueries to show in the Master Query.
--
NDNobbs
:
NDNobs:
I'm afraid you're gonna have to bite the bullet and learn a few new things here.
First, create 4 queries. One master query that has the EmployeeID and name.
You'll add the following 3 queries to it later.
Create a Hires query and name it qryHires. Make it a GROUP BY query and
add EmployeeID
Next, add your date field with this criteria
BETWEEN [Enter Start Date] AND [Enter End Date]
Finally add the EmployeeID again, setting it's a TOTAL property to COUNT.
Repeat this process, creating the qryPromo and qryResign.
Next, add these 3 queries to your main query and pull down the 3 count fields.
Make sure the joins between EmployeeID reutrns ALL rows from tblEmployee
in the main query.
It's hard to know how much detail to give. Not sure how familiar you are with
any of these functions. Give it a shot.
--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com
Thank you, but I'm an Access trainee. I need a little more direction.
Here's basically what I have....Employee Name, New Hires by Date, Promotion
by Date and Resignations by date. Because an employee may have had a
promotion within my date range, but his New Hire date is outside the range, I
want to only count the records in each column within my date range.
SQLs are a new thing to me. Where would it go?? Help!
--
NDNobbs
:
NDNobbs:
If I understand you correctly, your query with 3 columns might require
one query with 3 subqueries. Since you didn't post any sql, I'm making
this up as I go, but I imagine it to be something like this ...
Main Query
SELECT EmployeeID, Q01.Result01, Q02.Result02, Q03.Result03
FROM tblEmployee e
LEFT JOIN qryResults01 AS Q01 ON Q01.EmployeeID = e.EmployeeID
LEFT JOIN qryResults02 AS Q02 ON Q02.EmployeeID = e.EmployeeID
LEFT JOIN qryResults03 AS Q03 ON Q03.EmployeeID = e.EmployeeID
The LEFT JOINS assure that the whole list of EmployeeIDs will be returned
and if a Result exists,it will be shown.
Each of the 3 sub queries will look something like this ...
SELECT EmployeeID, [YourFieldOrAggregate] As Result01
FROM tblYourTable
WHERE [TheDateField] BETWEEN [Enter Start Date] AND [Enter End Date]
The beauty of this is that the parameters will only be requested once,even
though they are used in all 3 sub queries, provided you spell the prompts the same
in each one.
This could all be written as one big query, but it's harder to visualize, so I'll leave
that for another day.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com
I have 3 fields. I need to show all of them based on date criteria, but I
want the report to count only those records that fall within the date range.
(Since it is feasible for the record of one employee to show up in 2-3 of the
columns I need only specific data to be counted.)
How do I do this? Thank you
