Query for Obtaining Detail Records

[JWeaver]

I have a query that returns all records pertaining to a particular payroll
period for employees. I need to make a new query that will total the number
of hours worked by each employee and return ONLY those whose total number of
hours is over 80. I then want to use it in a report to obtain the detail of
the records that were used to make up over 80 hours. I have tried adding a
calculated field to do this but the total in this field is the same as the
number of hours shown for that particular week, and therefore, doesn't give
me what I want.

I have a Summary Report that I use that gives me the totals for everyone for
a particular payroll period but I haven't been able to change it to display
what I want either so I thought a new query might work better.

Any help you can give me would be appreciated.

 

[Klatuu]

Your question is confusing. First, you say:
returns all records pertaining to a particular payroll period

Then later:
but the total in this field is the same as the number of hours shown for
that particular week

The two statments above appear to contradict each other. Is a week not a
pay period?

 

[JWeaver]

I apologize. The pay period is 2 weeks, however, the employee may submit
more than 2 weeks worth of work for payment at one time, which results in
more than 80 hours of payment for that pay period. I need to be able to see
the detail for these employees.

I hope this is clearer.

 

[Klatuu]

How do you identify the work submitted by an employee for a two week pay
period?

 

[JWeaver]

Work is submitted on a Form for each week. They submit one Form for each
week of work they submit then we pay them for everything that they submit
since the last payroll date.

 

[Klatuu]

Seems like a totals query filtered on the pay period begin and end dates
should do it.
SELECT Employee, Sum(HoursWorked) AS TotalHours
FROM SomeTable
WHERE WorkDate Between #2/3/2006# And #4/18/2008# AND Sum(HoursWorked) >= 80
GROUP BY Employee;

or something like that.

 

[John Spencer]

Close, but you need an having clause

SELECT Employee, Sum(HoursWorked) AS TotalHours
FROM SomeTable
WHERE WorkDate Between #2/3/2006# And #4/18/2008#
GROUP BY Employee
HAVING Sum(HoursWorked) >= 80

If you now want to go back and get details you can modify the query slightly
and use it as a subquery in a where clause

SELECT *
FROM SomeTableWithDetails
WHERE Employee in (
SELECT Employee
FROM SomeTable
WHERE WorkDate Between #2/3/2006# And #4/18/2008#
GROUP BY Employee
HAVING Sum(HoursWorked) >= 80)
AND <<< Other criteria here >>>


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County