Weekly Overtime into Months

[neil_val]

Hi I have managed to suss out quite alot of my problem but my report /
query still requires some fine tuning.

Cuurently I have a report that works of the following queries:

SELECT qrySumHrsTotal.EmployeesName,
qrySumHrsTotal.SumOfSaturdayPremium AS [Sat Premium], qryPay3.PMonth
AS [Month], qryPay3.SumOfTotalHrs,
IIf([SumOfSaturdayPremium]>0,qryPay3!SumOfTotalHrs-
[SumOfSaturdayPremium],qryPay3!SumOfTotalHrs) AS ActualTotal,
qryPay3.HrsToDo, qryPay3.PayYear
FROM qryPay3 INNER JOIN qrySumHrsTotal ON qryPay3.EmployeesName =
qrySumHrsTotal.EmployeesName
GROUP BY qrySumHrsTotal.EmployeesName,
qrySumHrsTotal.SumOfSaturdayPremium, qryPay3.PMonth,
qryPay3.SumOfTotalHrs, qryPay3.HrsToDo, qryPay3.PayYear
HAVING (((qrySumHrsTotal.EmployeesName) Not Like "andy_milburn" And
(qrySumHrsTotal.EmployeesName) Not Like "Murray_Cowling" And
(qrySumHrsTotal.EmployeesName) Not Like "david_kirk" And
(qrySumHrsTotal.EmployeesName) Not Like "piet_vanzyl" And
(qrySumHrsTotal.EmployeesName) Not Like "mark_driscoll" And
(qrySumHrsTotal.EmployeesName) Not Like "pim_vanbaarsen" And
(qrySumHrsTotal.EmployeesName) Not Like "steven_ross" And
(qrySumHrsTotal.EmployeesName) Not Like "gary_kite" And
(qrySumHrsTotal.EmployeesName) Not Like "emma_tappern" And
(qrySumHrsTotal.EmployeesName) Not Like " gary_palmieri") AND
((qryPay3.PayYear)=[Year?]))
ORDER BY qryPay3.PMonth;

The problem with this is that in order for an employee to obtain
overtime they have to have over "x" amount of hours based on the
month.

I need the query to base it on the weekly overtime values but need
them grouped by month. I have another report that does just this:

SELECT qryWkHrs_1.EmployeesName, qryWkHrs_1.WeeklyHours,
IIf([WeeklyHours]>40,(IIf([WeeklyHours]>50,10,[WeeklyHours]-40)),0) AS
[40+], IIf([WeeklyHours]>50,[WeeklyHours]-50,0) AS [50+],
qryWkHrs_1.WkComDate, qryWkHrs_1.PayYear, "50" AS [Target Hours]
FROM qryPay2 INNER JOIN qryWkHrs_1 ON qryPay2.PayYear =
qryWkHrs_1.PayYear
GROUP BY qryWkHrs_1.EmployeesName, qryWkHrs_1.WeeklyHours,
qryWkHrs_1.WkComDate, qryWkHrs_1.PayYear, "50"
HAVING (((qryWkHrs_1.PayYear)=[Year?]))
ORDER BY qryWkHrs_1.WkComDate;

But I can't seem to get it to pick up hours over 50 per week and
calculate them into a monthly figure - ie

26/11/2007 52 hours - 2 hours O/T
03/12/2007 54 hours - 4 hours O/T
10/12/2007 47 hours - 0 hours O/T

Total O/T hours for month should be 6 hours.


Thanks