Calculating overtime in a complicated scenario

[dbar10]

OK I am in over my head again. I have developed a query to run my payroll reports from and generate an electronic file to send to payroll company. The following query works really good for our purposes except if there is overtime. Once a worker goes over 40 hours in [Time worked], needs to calculate 1.5 time the the ramaining for that worker for that week. I can't figure out how to get a running sum by WorkerID and only for the week from Sat or Day1 through Friday or Day 7. Sometimes a worker will turn in two weeks worth of timecards for this current payroll posting (which is weekly).

SELECT Schedule1.SchedID, Schedule1.WorkerID, [Worker table].LastName & ", " & [Worker table].FirstName & " " & [Worker table].Middle AS WorkerName, [Worker table].SSN, WorkStatus.WorkStatus, Jobs.Job, Category.Category, Schedule1.ClientID AS ClientIdent, [Client Table].LastName & ", " & [Client Table].FirstName & " " & [Client Table].Middle AS ClientName, SchedDay.Day, Schedule1.Date, Schedule1.Start, Schedule1.End, (DateDiff("n",[Start],[End]+IIf([End]<[Start],1,0))/60) AS [Hours worked], IIf([Salary]>0 And [Hours worked]>=4,8,IIf([Salary]>0 And [Hours worked]<4,[Hours worked],IIf([Salary]=0,[Hours worked],[Hours worked]))) AS [Hours Paid], Schedule1.Verified, Schedule1.VerifyDate, PayRateQry.WorkerPayrollQry.Hourly, PayRateQry.WorkerPayrollQry.Mileage, PayRateQry.Salary, PayRateQry.Period, PayRateQry.ClientID, PayRateQry.WorkClientPayrollQry.Hourly, PayRateQry.WorkClientPayrollQry.Mileage, IIf(IsNull(WorkClientPayrollQry.Hourly),WorkerPayrollQry.Hourly,WorkClientPayrollQry.Hourly) AS PayRate, Schedule1.Holiday, IIf([Holiday]=On,([Hours Paid]*[PayRate])*1.5,[Hours Paid]*[PayRate]) AS ShiftPay, IIf([Salary]>0,"Salary","Hourly") AS PayType, Schedule1.Mileage AS Miles, IIf(IsNull(WorkClientPayrollQry.Mileage),WorkerPayrollQry.Mileage,WorkClientPayrollQry.Mileage) AS MilesRate, [Miles]*[MilesRate] AS MileagePaid, Schedule1.Misc, Schedule1.MiscQty, Schedule1.PayMiscRate, [MiscQty]*[PayMiscRate] AS MiscPaid, Schedule1.[Pay / Bill]
FROM (Category INNER JOIN (WorkStatus INNER JOIN [Worker table] ON WorkStatus.WorkStatusID=[Worker table].WorkStatusID) ON Category.CategoryID=[Worker table].CategoryID) INNER JOIN (SchedDay INNER JOIN ([Client Table] INNER JOIN ((Schedule1 INNER JOIN Jobs ON Schedule1.JobID=Jobs.JobID) INNER JOIN PayRateQry ON Schedule1.WorkerID=PayRateQry.WorkerID) ON [Client Table].ClientID=Schedule1.ClientID) ON SchedDay.DayID=Schedule1.Day) ON [Worker table].WorkerID=Schedule1.WorkerID
WHERE (((Schedule1.Verified)=-1) And ((Schedule1.VerifyDate)>=Forms!WorkerPayRates!VerifiedFrom And (Schedule1.VerifyDate)<=Forms!WorkerPayRates!VerifiedTo) And ((PayRateQry.ClientID) Is Null Or (PayRateQry.ClientID)=Schedule1.ClientID) And ((Schedule1.[Pay / Bill])<>3 Or (Schedule1.[Pay / Bill])=4))
ORDER BY [Worker table].LastName & ", " & [Worker table].FirstName & " " & [Worker table].Middle, Schedule1.Date, Schedule1.Start;

I really hope that someone can shed some light on this as I am stuck. You guys have already helped me tremendously and I really appreciate all that you do.

 

[dbar10]

OK, I'm still working on this and I see from previoius posts that numbering the weeks is helpful,so I wrote that into my query. See if this helps.

SELECT Schedule1.SchedID, Schedule1.WorkerID, [Worker table].LastName & ", " & [Worker table].FirstName & " " & [Worker table].Middle AS WorkerName, [Worker table].SSN, WorkStatus.WorkStatus, Jobs.Job, Category.Category, Schedule1.ClientID AS ClientIdent, [Client Table].LastName & ", " & [Client Table].FirstName & " " & [Client Table].Middle AS ClientName, SchedDay.Day, Schedule1.Date, IIf([Date]>=(Forms!WorkerPayRates!PeriodFrom+7) And [Date]<=(Forms!WorkerPayRates!PeriodTo+7),0,IIf([Date]>=Forms!WorkerPayRates!PeriodFrom And [Date]<=Forms!WorkerPayRates!PeriodTo,1,IIf([Date]>=(Forms!WorkerPayRates!PeriodFrom-7) And [Date]<=(Forms!WorkerPayRates!PeriodTo-7),2,IIf([Date]>=(Forms!WorkerPayRates!PeriodFrom-14) And [Date]<=(Forms!WorkerPayRates!PeriodTo-14),3,4)))) AS Week, Schedule1.Start, Schedule1.End, (DateDiff("n",[Start],[End]+IIf([End]<[Start],1,0))/60) AS [Hours worked], IIf([Salary]>0 And [Hours worked]>=4,8,IIf([Salary]>0 And [Hours worked]<4,[Hours worked],IIf([Salary]=0,[Hours worked],[Hours worked]))) AS [Hours Paid], Schedule1.Verified, Schedule1.VerifyDate, PayRateQry.WorkerPayrollQry.Hourly, PayRateQry.WorkerPayrollQry.Mileage, PayRateQry.Salary, PayRateQry.Period, PayRateQry.ClientID, PayRateQry.WorkClientPayrollQry.Hourly, PayRateQry.WorkClientPayrollQry.Mileage, IIf(IsNull(WorkClientPayrollQry.Hourly),WorkerPayrollQry.Hourly,WorkClientPayrollQry.Hourly) AS PayRate, Schedule1.Holiday, IIf([Holiday]=On,([Hours Paid]*[PayRate])*1.5,[Hours Paid]*[PayRate]) AS ShiftPay, IIf([Salary]>0,"Salary","Hourly") AS PayType, Schedule1.Mileage AS Miles, IIf(IsNull(WorkClientPayrollQry.Mileage),WorkerPayrollQry.Mileage,WorkClientPayrollQry.Mileage) AS MilesRate, [Miles]*[MilesRate] AS MileagePaid, Schedule1.Misc, Schedule1.MiscQty, Schedule1.PayMiscRate, [MiscQty]*[PayMiscRate] AS MiscPaid, Schedule1.[Pay / Bill]
FROM (Category INNER JOIN (WorkStatus INNER JOIN [Worker table] ON WorkStatus.WorkStatusID = [Worker table].WorkStatusID) ON Category.CategoryID = [Worker table].CategoryID) INNER JOIN (SchedDay INNER JOIN ([Client Table] INNER JOIN ((Schedule1 INNER JOIN Jobs ON Schedule1.JobID = Jobs.JobID) INNER JOIN PayRateQry ON Schedule1.WorkerID = PayRateQry.WorkerID) ON [Client Table].ClientID = Schedule1.ClientID) ON SchedDay.DayID = Schedule1.Day) ON [Worker table].WorkerID = Schedule1.WorkerID
WHERE (((Schedule1.Verified)=-1) AND ((Schedule1.VerifyDate)>=[Forms]![WorkerPayRates]![VerifiedFrom] And (Schedule1.VerifyDate)<=[Forms]![WorkerPayRates]![VerifiedTo]) AND ((PayRateQry.ClientID) Is Null Or (PayRateQry.ClientID)=[Schedule1].[ClientID]) AND ((Schedule1.[Pay / Bill])<>3 Or (Schedule1.[Pay / Bill])=4))
ORDER BY [Worker table].LastName & ", " & [Worker table].FirstName & " " & [Worker table].Middle, Schedule1.Date, Schedule1.Start;

 

[dbar10]

I take it by the lack of response that this is as tough as I thought. I found this link for MS

http://support.microsoft.com/kb/205183

and it shows how to do runsum query with CatID and Units. But I need to run on three criteria listed here: [WorkerID], [Week] and Running Sum of [Hours Paid]. Can that be done?

 

[dbar10]

I made the Module based on instruction from MS site call modGrpRunSum:
ption Compare Database

Option Explicit

Function fncRunSum(lngCatID As Long, lngUnits As Long) As Long
'Variables that retain their values.
Static lngID As Long
Static lngAmt As Long

If lngID <> lngCatID Then
'If the current ID does not match the last ID, then (re)initialize.
lngID = lngCatID
lngAmt = lngUnits
Else
'If the current ID matches the last, keep a running sum for the ID.
lngAmt = lngAmt + lngUnits
End If

'Pass the running sum back to the query.
fncRunSum = lngAmt
End Function


Then I changed my query to:
SELECT Schedule1.SchedID, Schedule1.WorkerID, [Worker table].LastName & ", " & [Worker table].FirstName & " " & [Worker table].Middle AS WorkerName, [Worker table].SSN, WorkStatus.WorkStatus, Jobs.Job, Category.Category, Schedule1.ClientID AS ClientIdent, [Client Table].LastName & ", " & [Client Table].FirstName & " " & [Client Table].Middle AS ClientName, SchedDay.Day, Schedule1.Date, IIf([Date]>=(Forms!WorkerPayRates!PeriodFrom+7) And [Date]<=(Forms!WorkerPayRates!PeriodTo+7),0,IIf([Date]>=Forms!WorkerPayRates!PeriodFrom And [Date]<=Forms!WorkerPayRates!PeriodTo,1,IIf([Date]>=(Forms!WorkerPayRates!PeriodFrom-7) And [Date]<=(Forms!WorkerPayRates!PeriodTo-7),2,IIf([Date]>=(Forms!WorkerPayRates!PeriodFrom-14) And [Date]<=(Forms!WorkerPayRates!PeriodTo-14),3,4)))) AS Week, Schedule1.Start, Schedule1.End, (DateDiff("n",[Start],[End]+IIf([End]<[Start],1,0))/60) AS [Hours worked], IIf([Salary]>0 And [Hours worked]>=4,8,IIf([Salary]>0 And [Hours worked]<4,[Hours worked],IIf([Salary]=0,[Hours worked],[Hours worked]))) AS [Hours Paid], fncRunSum([Schedule1].[WorkerID],[Hours Paid]) AS HoursSum, Schedule1.Verified, Schedule1.VerifyDate, PayRateQry.WorkerPayrollQry.Hourly, PayRateQry.WorkerPayrollQry.Mileage, PayRateQry.Salary, PayRateQry.Period, PayRateQry.ClientID, PayRateQry.WorkClientPayrollQry.Hourly, PayRateQry.WorkClientPayrollQry.Mileage, IIf(IsNull(WorkClientPayrollQry.Hourly),WorkerPayrollQry.Hourly,WorkClientPayrollQry.Hourly) AS PayRate, Schedule1.Holiday, IIf([Holiday]=On,([Hours Paid]*[PayRate])*1.5,[Hours Paid]*[PayRate]) AS ShiftPay, IIf([Salary]>0,"Salary","Hourly") AS PayType, Schedule1.Mileage AS Miles, IIf(IsNull(WorkClientPayrollQry.Mileage),WorkerPayrollQry.Mileage,WorkClientPayrollQry.Mileage) AS MilesRate, [Miles]*[MilesRate] AS MileagePaid, Schedule1.Misc, Schedule1.MiscQty, Schedule1.PayMiscRate, [MiscQty]*[PayMiscRate] AS MiscPaid, Schedule1.[Pay / Bill]
FROM (Category INNER JOIN (WorkStatus INNER JOIN [Worker table] ON WorkStatus.WorkStatusID = [Worker table].WorkStatusID) ON Category.CategoryID = [Worker table].CategoryID) INNER JOIN (SchedDay INNER JOIN ([Client Table] INNER JOIN ((Schedule1 INNER JOIN Jobs ON Schedule1.JobID = Jobs.JobID) INNER JOIN PayRateQry ON Schedule1.WorkerID = PayRateQry.WorkerID) ON [Client Table].ClientID = Schedule1.ClientID) ON SchedDay.DayID = Schedule1.Day) ON [Worker table].WorkerID = Schedule1.WorkerID
WHERE (((Schedule1.Verified)=-1) AND ((Schedule1.VerifyDate)>=[Forms]![WorkerPayRates]![VerifiedFrom] And (Schedule1.VerifyDate)<=[Forms]![WorkerPayRates]![VerifiedTo]) AND ((PayRateQry.ClientID) Is Null Or (PayRateQry.ClientID)=[Schedule1].[ClientID]) AND ((Schedule1.[Pay / Bill])<>3 Or (Schedule1.[Pay / Bill])=4))
ORDER BY [Worker table].LastName & ", " & [Worker table].FirstName & " " & [Worker table].Middle, Schedule1.Date, Schedule1.Start;

This works for running a total of hours but I need to also include seperate weeks for overtime. Also, If the hours worked are 7.5 it rounds up to 8 in each record. Any help? Now, I am stuck.

 

[MazeWorX]

lack of response is probably due to the fact that you answering you own post lol.

However you could use DSum to sum the hrs

HTH << MaZeWorX >> Remember amateurs built the ark - professionals built the Titanic [flush]