Query - Using SUM in a query where unique dates are present

[AJ]

HELP!

I have a very simple DB... 1 table collects staff info, including a pay rate.
A second table collects a breakdown of how the spent their hours each day,
as well as the date.

I am working towards building a report that will pull up a date range (pay
period), show a sum of all the hours worked, and calculate their pay...

I am stuck on the Query.
I build a Query, with a condition in the date ' Between [Start Date] and
[End Date] '... this pulls up the date range that I ask for...
But it will not allow me to use the sum function because each date is unique.
If I remove the date from the query, I get what I am looking for... but the
SUM is now a sum of the persons hours for the entire year... rather than a
specified pay period.

Is there any way to generate a query that will allow you to search for a
date range, and then disregard the unique dates while calculating the sum's
of each labor category?

Is my question clear enough.... I think i may have even confused myself.

 

[Ken Sheridan]

If you want to return just the sum of hours worked per employee over the date
range. a simple aggregating query as described by Bob, grouped on employee,
restricted on the dates and summing the hours is all that's needed, e.g.

PARAMETERS
[Start Date] DATETIME,
[End Date] DATETIME;
SELECT Employees.EmployeeID, FirstName,
LastName, LaborCategory,
SUM(HoursWorked) As TotalHours
FROM Employees INNER JOIN WorkLog
ON Employees.EmployeeID = WorkLog.EmployeeID
WHERE WorkDate BETWEEN [Start Date] AND [End Date]
GROUP BY EmployeeID, FirstName, LastName, LaborCategory;

If you also want to return the individual dates in the result table you'll
need to include a subquery in the SELECT clause, e.g.

PARAMETERS
[Start Date] DATETIME,
[End Date] DATETIME;
SELECT DISTINCT EmployeeID, FirstName,
LastName, WorkDate, LaborCategory,
(SELECT SUM(HoursWorked)
FROM WorkLog AS WL2
WHERE WL2.EmployeeID = WL1.EmployeeID
AND WL2.LaborCategory = WL1.LaborCategory
AND WL2.WorkDate BETWEEN [Start Date] AND [End Date])
As TotalHours
FROM Employees INNER JOIN WorkLog AS WL1
ON Employees.EmployeeID = WL1.EmployeeID
WHERE WL1.WorkDate BETWEEN [Start Date] AND [End Date];

where Employees is the table of staff and WorkLog the table of their time
worked, the two being related on EmployeeID columns. In the second query the
two instances of the WorkLog table are distinguished by the aliases WL1 and
WL2, allowing the subquery to be correlated with the outer query so that it
returns the sum of hours per employee/labour category.

Note that in both cases date/time parameters should be declared as above.
Otherwise a parameter value entered in short date format could be
misinterpreted as an arithmetical expression and give the wrong results.

For the latter however it would normally not all be done in the query, but
the aggregation would be done in the report in a group footer, basing the
report on a simple query which returns all rows within the date range.

One thing to be careful of when restricting a query on a date range using a
BETWEEN….AND operation is that none of the dates include a non-zero time of
day. There is no such thing in Access as a date value per se only a
date/time value. Unless you've taken specific steps in the table definition
to guard prevent it a value with a non-zero time of day can get into a
date/time column inadvertently without you being aware of it. This will
cause rows with date/time values including a non-zero time of day on the last
day of the range not to be returned. You can guard against this possibility
however by defining the date range differently in the query:

WHERE WorkDate >= [Start Date]
AND WorkDate < DATEADD("d",1,[End Date])

This returns rows where the date is on or after the start date and before
the day after the end date, so rows on the final day will be returned even if
the values include a non-zero time of day.

Ken Sheridan
Stafford, England

HELP!

I have a very simple DB... 1 table collects staff info, including a pay rate.
A second table collects a breakdown of how the spent their hours each day,
as well as the date.

I am working towards building a report that will pull up a date range (pay
period), show a sum of all the hours worked, and calculate their pay...

I am stuck on the Query.
I build a Query, with a condition in the date ' Between [Start Date] and
[End Date] '... this pulls up the date range that I ask for...
But it will not allow me to use the sum function because each date is unique.
If I remove the date from the query, I get what I am looking for... but the
SUM is now a sum of the persons hours for the entire year... rather than a
specified pay period.

Is there any way to generate a query that will allow you to search for a
date range, and then disregard the unique dates while calculating the sum's
of each labor category?

Is my question clear enough.... I think i may have even confused myself.