Query for Obtaining Detail Records

  • Thread starter Thread starter JWeaver
  • Start date Start date
J

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.
 
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?
 
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.
 
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.
 
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.
 
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
 
Back
Top