Need to determine overtime in query 1

[dbar10]

I have been working on this now for weeks and I am stuck. I hope that someone can help me with a solution. The following queries are working together to calculate hours worked and rate of pay for a worker at a particular client. Each worker may have different rates of pay at each individual client. All this works great up to the point that I need to figure overtime for each worker. I need to include a running sum of hours worked so I can determine the hours and rate of pay that is entitled to overtime. (Over 40 hours in a week.) You will notice that I have labeled each record with a week number because sometimes workers turn in time cards late and the overtime week calculation needs to take that into consideration as well. This is too complicated to get my head around now. I can’t find any way to get a running sum in a query. I tried outputting it to a report but the report that I need groups the hours worked by worker then each client. This separates the dates so that I can’t determine when the overtime starts. Ideally I need to determine this in the query if possible.
Final query is listed next with supporting queries and tables below it. Please help!!!!


SchedulePayrollQry:
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, IIf([Cancel]=0,(DateDiff("n",[Start],[End]+IIf([End]<[Start],1,0))/60),0) 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.Cancel, Schedule1.Verified, Schedule1.VerifyDate, WorkClientPayrollQry.Hourly, WorkerPayrollQry.Hourly, 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, WorkerPayrollQry.WorkEligOvertime, Schedule1.[Pay / Bill], WorkerPayrollQry.WorkEligHoliday
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 (WorkerPayrollQry INNER JOIN (WorkClientPayrollQry RIGHT JOIN (Schedule1 INNER JOIN Jobs ON Schedule1.JobID = Jobs.JobID) ON (WorkClientPayrollQry.ClientID = Schedule1.ClientID) AND (WorkClientPayrollQry.WorkerID = Schedule1.WorkerID)) ON WorkerPayrollQry.WorkerID = Schedule1.WorkerID) ON [Client Table].ClientID = Schedule1.ClientID) ON SchedDay.DayID = Schedule1.Day) ON [Worker table].WorkerID = Schedule1.WorkerID
WHERE (((Schedule1.Date)<=[Forms]![WorkerPayRates]![PeriodTo]) AND ((Schedule1.Verified)=-1) AND ((Schedule1.VerifyDate)>=[Forms]![WorkerPayRates]![VerifiedFrom] And (Schedule1.VerifyDate)<=[Forms]![WorkerPayRates]![VerifiedTo]) AND ((Schedule1.[Pay / Bill])=1 Or (Schedule1.[Pay / Bill])=2))
ORDER BY [Worker table].LastName & ", " & [Worker table].FirstName & " " & [Worker table].Middle, Schedule1.Date, Schedule1.Start;
WorkerPayrollQry
SELECT WorkerWagetbl.DefaultWageID, WorkerWagetbl.WorkerID, WorkerWagetbl.AssignDateHr, WorkerWagetbl.ExpireDateHr, WorkerWagetbl.Hourly, WorkerWagetbl.Mileage, WorkerWagetbl.Salary, WorkerWagetbl.Period, [Worker table].WorkEligHoliday, [Worker table].WorkEligOvertime
FROM WorkerWagetbl INNER JOIN [Worker table] ON WorkerWagetbl.WorkerID = [Worker table].WorkerID
WHERE (((WorkerWagetbl.AssignDateHr)<=[Forms]![WorkerPayRates]![VerifiedFrom]) AND ((WorkerWagetbl.ExpireDateHr) Is Null Or (WorkerWagetbl.ExpireDateHr)>[Forms]![WorkerPayRates]![VerifiedFrom]));
Tables for WorkerPayrollQry

WorkerWagetbl:

DefaultWageID AutoNumber
WorkerID Number
AssignDateHr Date/Time Short date
ExpireDateHr Date/Time ShortDate
Hourly Currency
Mileage Currency
Salary Currency
Period Number Lookup: Hour/Week/Month

Joined to: Worker table

WorkerID AutoNumber
WorkStatusID Number - Lookup
LastName Text
FirstName Text
Middle Text
etc. other demographic info

WorkClientPayrollQry:

SELECT WorkClientWagetbl.WorkerID, WorkClientWagetbl.ClientID, WorkClientWagetbl.AssignDateHr, WorkClientWagetbl.ExpireDateHr, WorkClientWagetbl.Hourly, WorkClientWagetbl.Mileage, WorkClientWagetbl.Period
FROM WorkClientWagetbl
WHERE (((WorkClientWagetbl.AssignDateHr) Is Null Or (WorkClientWagetbl.AssignDateHr)<=[Forms]![WorkerPayRates]![VerifiedFrom]) AND ((WorkClientWagetbl.ExpireDateHr) Is Null Or (WorkClientWagetbl.ExpireDateHr)>=[Forms]![WorkerPayRates]![VerifiedFrom]));

WorkClientWagetbl:

ClientWageID AutoNumber
WorkerID Number
ClientID Number
AssignDateHr Date/Time Short date
ExpireDateHr Date/Time Short Date
Hourly Currency
Mileage Currency
Period Number Lookup – Hourly or Salary

Schedule1: (table)

SchedID AutoNumber
MasterSchedID Number
DaySchedID Number
ClientID Number
WorkerID Number
AuthID Number
JobID Number
ProgramID Number
Date Date/Time Short Date
Day Number
Start Date/Time Short Date
End Date/Time Short Date
Verified yes/No
VerifiedDate Date/Time ShortDate
Cancel Yes/No
Reason Number Lookup
NoNeed Yes/No
CancelMaster Yes/No
CancelMasterDate Date/Time Short Date
Holiday Yes/No
Mileage Number
Misc Number Lookup
MiscQty Number
PayMiscRate Currency
Pay/Bill Number Lookup

 

[dbar10]

We will be exporting this out to a payroll company but they still need each record by shift and payrate with the overtime rate if overtime is hit by the worker. Don't need totals by week just total hrs worked and payrate for those hours. If overtime they need to know that. This is a very complicated scenario. I know my scenario above didn't come out very well after posting it. But maybe you could cut and paste in Word and straighten it up so you can see the columns better.

 

[kjv1611]

Okay, so basically, the way the overtime system here works is that once the person goes over 40 hours for the week, then any hours worked over 40 are time and a half?

So, then:

If your table is setup like this (same suggestion, but with a couple fields added):
EmployeeID
TimeEntryID - Make this the primary ID for the table
Date
Client
Rate
Shift
Day
TimeBegin
TimeEnd
Hours
PayEarned
RunningWkTotal

Then if you wanted to fix it up in a VBA procedure - if you've got just a small number of employees - this would work until you grew to a much bigger size... again, at that time, you'd want to go to another solution altogether anyhow.

And I don't really have the time to code the whole thing out right now... but you could basically modify the above... or use some SQL embedded within the VBA... there are at least a couple different methods that should work..

If I can get back to it today, I can possibly shoot off a few more ideas... Of course, by that time, surely someone else will have give another good idea or two.

--

"If to err is human, then I must be some kind of human!" -Me

 

[dbar10]

Hey kjv,

I think we are going the wrong direction here. I think.
I can use the queries I already have to generate a report to do everything I need. That report would list all the shifts worked by each worker for a given week and have a running sum to determine overtime and calculate the overtime rate. The problem is that the report will only work if I group on workers only. The report then does the calculations I need. That will work fine. My bosses want a report that takes those results and then group it by client. Since I can't get that from the query I was thinking maybe I could use the data from the By Worker report and link that to another report that would group by worker and then client. With what you are proposing, it looks like a manual way of storing the data in tables and but I don't see how you are going to get a running sum there either. I'll keep working on this. Thank you very much for your assistance.

 

[kjv1611]

Since I can't get that from the query I was thinking maybe I could use the data from the By Worker report and link that to another report that would group by worker and then client.

Yeah, I was going back to if you could fix it back from the start rather than trying to patch it all together.

If you are just looking for a report, and having issues there, then it's not really a Query/JetSQL issue anyhow, but rather a report issue.

You might want to try posting over in:
forum703

And then specifically asking if you can build a report off of another report. I think that's the bottom line you're getting at. Whether or not it's the 'best' method, it's what you're wanting to do on this occasion to get the job done.

Either way, I hope you get it worked out.

Of course, if you can't get the reporting you want done all in Access, but yet you have all the raw data, you could just spit it out to Excel (assuming record/row count not too high), and do the summaries, reporting, etc there - perhaps with a pivottable report.

Of course, a pivot table is something that can be done in Access as well, I just know I've not personally messed with those in Access.

--

"If to err is human, then I must be some kind of human!" -Me

 

[dhookom]

I would probably just add a field to Schedule1 to store the number of overtime hours and default it to 0. Then run code that opens the table and loops through records by employee and week totaling the hours. When the total reaches 40, start storing the number of OT hours in the appropriate field. This code might need to be run whenever the Schedule1 records get updated for an employee.

Duane
Hook'D on Access
MS Access MVP

 

[dbar10]

dhookum, Now that sounds like the best idea yet. I am going to have to think about how to right such a code though. Doesn't sound easy, but if that's possible it would be awesome. Thanks

 

[dhookom]

I expect I am not including a lot of your business rules, however your code might look something like:

Dim rs as DAO.Recordset
Dim db As DAO.Database
Dim strSQL s String
Dim intWeek as Integer
Dim dblWeekHours as Double
Dim lngWorkerID as Long

set db = Currentdb
strSQL = "SELECT WorkerID, [Date], DatePart('ww',[Date]) as WeekOf, Start, End, OTHours " & _
  "FROM Schedule1 " 
strSQL = strSQL & "WHERE [Date] Between #" & Me.txtStartDate & "# AND #" & Me.txtEndDate & "# "
strSQL = strSQL & "ORDER BY WorkerID, [Date], Start "
set rs = db.OpenRecordset(strSQL)
With rs
   .MoveFirst
   Do Until .EOF
      If !WorkerID <> lngWorkerID OR !WeekOf <> intWeek Then
          lngWorkerID = !WorkerID
          intWeek = !WeekOf
          dblWeekHours = 0
        Else
          dblWeekHours = dblWeekHours + DateDiff("n",!Start, !End)/60
          If dblWeekHours > 40 Then
             .Edit
             !OTHours = DateDiff("n",!Start, !End)/60
             .Update
          End If
      End If
      .MoveNext
   Loop
   .Close
End With
set rs = Nothing
set db = Nothing

Duane
Hook'D on Access
MS Access MVP

 

[dbar10]

dhookum, Thank you very much. I can see the light!!!!!
I will work with this tomorrow and see what comes of it.

 

[dbar10]

dhookum, I need your help a little more if it's okay.

Your idea is awesome but I need a little help. I am incorporating your code in the On Click command on the Verify check box on my scheduling screen. The Payroll clerk goes through the schedule and timecards when they are received and clicks verify when the schedule and the timecard matches or edits to make them match. Now what I would like to do is have your code look at the date of that record and look at every time in that week to calculate the overtime. I have a function which identifies the week period and I think that it could be incorporated into this code but I don't know how. Here is the function:
Option Compare Database

Public Function fStartofWeek(somedate, Optional StartDayNo = 2)
'Returns the date of the first day of the current week
'Default is to use the system-defined start day no of 2
'(corresponding to Monday). Range of 1 to 7 for this value.
'
Dim intDayOfWeek As Integer, intAdjustDays As Integer
If Not IsNull(somedate) Then
intDayOfWeek = Weekday(somedate)
intAdjustDays = intDayOfWeek - StartDayNo
If intAdjustDays < 0 Then
intAdjustDays = intAdjustDays + 7
End If
fStartofWeek = CDate(somedate - intAdjustDays)
End If

End Function

Now I use this with the following calculation:
=fStartofWeek(Me.[TextDate],7) (TextDate is the date on the record in Schedule1)
I also only want to look at records that Me.Verify = Yes

Can you help me? You are awesome!!

 

[dhookom]

I would think you would determine if there were "almost" 40 hours prior to the current record in the current week. You can add in the check for verified and figure out how you would fix this if an earlier record changed from not verified to verified.

I would probably code all of this into a standard module where a date range and possibly one or more workers are updated.

Dim strSQL as String
Dim db as DAO.Database
Dim rs as DAO.Recordset
set db = currentdb
strSQL = "SELECT Sum([HoursWorkedField]) FROM Schedule1 " & _
   "WHERE WorkerID = " & Me.txtWorkerID & " AND " & _
   "[Date]+[Start] >= #" & Me.txtStartOfWeek & "# AND " & _
   "[Date]+[Start] < #" & Me.[TextDate]+ Me.[Start] & "# "
Set rs = db.OpenRecordset(strSQL)
If Not rs.EOF then
    'do something to OTHours worked field if the record _
     pushes the worker over 40 hours
End if
rs.Close
set rs = Nothing
set db = Nothing

Duane
Hook'D on Access
MS Access MVP

 

[dbar10]

dhookum, your code got me to thinking and I realized that I could use the query that pulls the schedule records put in some hidden calculation fields in the schedule record and then when click verify apply the results to the Shedule1 table. I will have to add to Schedule1 fields like HrsWorked which will only go up to 40 for the week, then OTHours for everything over, then a HrsPaid checkbox to be activated when the record is posted and OTPaid when record is posted. Then I will have to make a way to update the record if later edits after posting change the OTHours so that it can be recalculated and paid correctly.

Got my work ahead of me. I could not have done this without your help. Thank you very much for getting me on the right track.

 

[dbar10]

DHookum, I have been working with this and this is what I have done. In the Schedule1 table I added:
HoursWorked double
RegHrs Double
OTHrs Double
RegHrsPaid Date 'these will be populated w/ Date() when Verified
OTHoursPaid ShortDate

From Forms!SchedSearchList! ScheduleWorkerfrm.Form.Verify , I want to run this:
(I am sure that I have not got this right, help please)
Private Sub Verify_AfterUpdate()
Me.Refresh

Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
strSQL = "SELECT Sum([HoursWorked]) FROM Schedule1 " & _
"WHERE WorkerID = " & Me.WorkerID & " AND " & _
"[Date]+[Start] >= #" & Me.txtStartofWeek & "# AND " & _
"[Date]+[Start] < #" & Me.[TextDate] + Me.[Start] & "# "
Set rs = db.OpenRecordset(strSQL)
If Not rs.EOF Then
'do something to OTHours worked field if the record _
pushes the worker over 40 hours
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

If Not rs.EOF Then
'do something to OTHours worked field if the record _
pushes the worker over 40 hours

I want to assign the Hours up to 40 for the week in the RegHours field in the Schedule1 table then assign the hours over 40 for the week to the OTHours in the Schedule1 table. Something like:
For Reg Hours: =IIf(RunSum <=40,[Hours worked],IIf(RunSum >40,[Hours worked]-[OTHrs],0))
For OTHours: =IIf(RunSum <=40,Null,IIf(RunSum >40,RunSum -40,0))

 

[dhookom]

What happens when you try it?
I don't know what RunSum is.


Duane
Hook'D on Access
MS Access MVP

 

[dbar10]

I have a macro assigned to the AfterUpdate of SchedSearchList!ScheduleWorkerfrm!Form.TextStart and TextEnd and Verify. This is assigning the hours worked
to Schedule1.HoursWorked just fine.
But when I run the code nothing happens at this point. Not getting any errors either. Should it work the way it is?
The RegHours and OTHours fields of Schedule1 remains 0.
RunSum is just my term for the number of hours currently in the current week.

 

[dhookom]

There was nothing in my code that updated anything.

You need to learn how to think about how you would use the runsum and current hours worked to determine how many hours/minutes should be regular or overtime. Think of this in terms of one step that follows another. This is your pseudo code.

Then write your actual code the calculates the time and updates the values in your record.

Duane
Hook'D on Access
MS Access MVP

 

[dbar10]

Thanks DHookum. My problem is I don't understand writing code that much. I've never had to go into it that deep. But, I guess I will do some research and see what I can figure out. So, the code that you wrote, does it just get a Sum of the hours worked up to that record? Is there a way for me to see the end result? Sorry to be such a pain.

 

[dhookom]

strSQL is a SQL statement that is used in the recordset to pull the sum of hours worked up to that point in the week.

strSQL = "SELECT Sum([HoursWorked]) FROM Schedule1 " & _   "WHERE WorkerID = " & Me.WorkerID & " AND " & _   "[Date]+[Start] >= #" & Me.txtStartofWeek & "# AND " & _   "[Date]+[Start] < #" & Me.[TextDate] + Me.[Start] & "# "
Set rs = db.OpenRecordset(strSQL)

You can assign the returned value to a memory variable with code like:

   dblPrevTime = rs(0)
   'display it
   MsgBox "Previous time: " & dblPrevTime

You can then subtract dblPrevTime from 40 hours to see if there is time that is regular or OT. You need to then update the controls on the form that display/store the regular and OT. Again, the SQL statement probably needs to filter out more records.


Duane
Hook'D on Access
MS Access MVP

 

[dbar10]

Oh gotcha. Thanks you very much for all your help. I think that sometimes I just get to overwhelmed and forget to step back and think it through "step by step". you have been a big help once again. I appreciate this site more than you know.