Solved Query to return total time by weeks in a month

[raziel3]

Hello everyone,
Still working on my Payroll db and I am looking for a way to make a query to return the total hours worked for weekly employees.

Payroll ends on a Saturday and for each employee I want to generate a report to get the hours worked for each week in a month because statutory deductions are done monthly.

So far I've come up with this to get all the Saturdays in a month and results fills a combobox.

Private Sub WkEnd()

Me.cboPayEnd.RowSource = ""
Me.cboPayEnd.RowSourceType = "Value List"

BegDate = DateSerial(Me.cboYear, Me.cboMonth, 1)
EndDate = DateSerial(Me.cboYear, Me.cboMonth2 + 1, 0)

For intDay = BegDate To EndDate
    If Weekday(intDay) = vbSaturday Then
        Me.cboPayEnd.AddItem Format(intDay, "Medium Date")
    End If
Next intDay

End Sub

But how do I execute it in the query GUI to look something like this

+-----+---------------+--------------+--------------+-----------+
| EID | SumOfSTDHours | SumOfDTHours | SumOfOTHours |   WKEnd   |
+-----+---------------+--------------+--------------+-----------+
|  13 |            37 |            0 |            0 | 1/1/2022  |
|  13 |         47.25 |            0 |            0 | 8/1/2022  |
|  13 |         54.25 |            0 |            0 | 15/1/2022 |
|  13 |            45 |            0 |            0 | 22/1/2022 |
|  13 |            37 |            0 |            0 | 29/1/2022 |
|  14 |            40 |            0 |            0 | 1/1/2022  |
|  14 |            46 |            0 |            0 | 8/1/2022  |
|  14 |         47.25 |            0 |            0 | 15/1/2022 |
|  14 |            47 |            0 |            0 | 22/1/2022 |
|  14 |         47.75 |            0 |            0 | 29/1/2022 |
|  15 |            48 |            0 |            0 | 1/1/2022  |
|  15 |            40 |            0 |            0 | 8/1/2022  |
|  15 |            47 |            0 |            0 | 15/1/2022 |
|  15 |            48 |            0 |            0 | 22/1/2022 |
|  15 |            40 |            0 |            0 | 29/1/2022 |
+-----+---------------+--------------+--------------+-----------+

Payroll is from Sunday to Saturday. If for example, the month starts on a Saturday like January 2022, payroll period will capture all the data between December 26th, 2021 to January 1st, 2022

 

[The_Doc_Man]

If you have daily "hours worked" data, make a query that shows your daily hours worked and that includes a DatePart function to return a week number.

DatePart function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

Then as a second layer query (i.e. a query using the 1st query in a FROM clause), you can do an SQL SUM(<<hours-field-name>>) with a GROUP BY of the week number and you can use either MIN or MAX of the date field to tell you the day on which the week began or ended. (Those work well in a GROUP BY situation.)

NOTE that you can choose which day of the week delineates the weeks. However, you say payroll ends on Saturday so therefore begins on Sunday. Since that is the default case, you probably won't have to worry about the extra parameters. Read the link carefully to determine the situations where you might get an anomalous 53rd week, but otherwise this sounds like a simple enough approach.

 

[plog]

TimeSheet.TSID=1677 -> Someone worked negative hours (STDHours=-4.00). I don't work in HR, is that possible?

They call it a Date/Time field for a reason, it can hold both pieces of data. My guess is WDate goes away and TimeIN and TimeOut needs to hold the full date and time values not just time.

Then, since people can work from Saturday night to Sunday morning--what week do those hours belong to?

 

[raziel3]

TimeSheet.TSID=1677 -> Someone worked negative hours (STDHours=-4.00). I don't work in HR, is that possible?

That was a data entry error. It should have been 15:00 not in 3:00 in the TimeOut field

Then, since people can work from Saturday night to Sunday morning--what week do those hours belong to?

Now you see why I need WDATE. Payroll, IMO, should always have a human element in it to verify the data. If I only relied on formulas how would I know what pay period to put the employee in. The supervisor will make that decision. Also if I was only going by the clocking machine, how would I know if an employee just clocked in, left the compound and just came back to clock in. How would I know if the employee had an emergency and clocked out early but the supervisor decided to pay him/her for the whole day?

There are so many variables when only using the Time In and Time Out as your source data it becomes untrustworthy.

 

[plog]

If I only relied on formulas how would I know what pay period to put the employee in.

Because computers are excellent, no; perfect at implementing algorithms. You seem to think that having 2 error prone points in the system (people inputting data and supervisors deciding when weeks start) will cancel each other out. In fact it will double the chances of errors. I didn't manually go thru your records 1 by 1 to find that error, I used Access to sort your data and found it easily.

There are so many variables when only using the Time In and Time Out as your source data it becomes untrustworthy.

You also seem to be arguing that your system isn't governed by rules. You certainly don't just roll dice and use them to decide what to pay people. You have calculations, rules, algorithms to do that. My prior suggestions were to make changes to your system so that it could better implement those rules.

 

[jdraw]

Further to advice and comments already provided, you seem to have some business rules that aren't addressed. Data entry values are a reflection of "what" happened. In effect, a documented fact to be stored and processed. Seems your documented facts can be altered by "the supervisor" at his/her discretion. You might include a process(es) in your workflow where the supervisor "audits the initial data entry and adjusts as necessary" before you do any sort of reporting or production procedures.
Another option is to include validation and reasonableness checks as the data is entered and before it is saved.
For example, you should NOT have two consecutive TimeIns (or TimeOuts) for any employee.

Also if I was only going by the clocking machine.... I think that was the intended purpose of such machines. What else would it be for?

 

[raziel3]

Ok. The workflow goes like this
- The week ends and the data is pulled from the clocking machine
- The data is printed out and the supervisor verifies the TimeIn and TimeOut. (this is a manual process)
- The sheet is signed and then given to me for data entry.
- Any allowances, time deductions, modifications are done at the Supervisor Level.

Just this morning 10 employees were unable to clock in because the Power Supply for the machine was faulty and it took half the day to source a new one. Now when data is pulled from the clocking machine for this week end, it would look like those 10 employees did not reach to work on time. How would you suggest the computer "implement the algorithm"?

 

[plog]

You got me. When the power goes out its time to turn to the dice and let those determine what people get paid. Much better than having a process in place that fixes your source data.

 

[arnelgp]

which one are the Weekly employees (hourly or monthly?)

 

[raziel3]

which one are the Weekly employees (hourly or monthly?)

Hourly.

I tried this:

SELECT TimeSheet.EID, Sum(TimeSheet.STDHours) AS SumOfSTDHours, Sum(TimeSheet.DTHours) AS SumOfDTHours, Sum(TimeSheet.OTHours) AS SumOfOTHours, DatePart("ww",[WDate],2) AS WeekEnd

FROM TimeSheet

GROUP BY TimeSheet.EID, DatePart("ww",[WDate],3);

The pay period runs from Sunday to Saturday. Total time for 1-Jan-2022 should total all days worked from 26-Dec-2021 to 1-Jan-2022 but the query only totals time from 1-Jan-2022. Also, not all employees work on Saturday so I can't use the Time table to total by Saturdays if it does not exist in the table.

I was thinking to have a query generate all the Saturdays for a month and then join the table (TimeSheet) that contains the times to that.

 

[raziel3]

Update

I think I did it

First I did this to return the following Saturday based on the WDate

Public Function getSat(sDate) As Date

Select Case Weekday(sDate)
    Case vbSunday
        getSat = DateAdd("d", 6, sDate)
    Case vbMonday
        getSat = DateAdd("d", 5, sDate)
    Case vbTuesday
        getSat = DateAdd("d", 4, sDate)
    Case vbWednesday
        getSat = DateAdd("d", 3, sDate)
    Case vbThursday
        getSat = DateAdd("d", 2, sDate)
    Case vbFriday
        getSat = DateAdd("d", 1, sDate)
    Case vbSaturday
        getSat = sDate
End Select

End Function

Then I made a totals query to sum all hours worked

SELECT TimeSheet.EID, Sum(TimeSheet.STDHours) AS SumOfSTDHours,
Sum(TimeSheet.DTHours) AS SumOfDTHours,
Sum(TimeSheet.OTHours) AS SumOfOTHours,
getSat([WDate]) AS PayEnd
FROM TimeSheet
GROUP BY TimeSheet.EID, getSat([WDate]);

It works but I don't know if it is the correct approach. Suggestions/critiques?

Now I just have to crosstab it to be displayed like this

+-----+--------+--------+---------+---------+---------+
| EID | 1/1/22 | 8/1/22 | 15/1/22 | 22/1/22 | 29/1/22 |
+-----+--------+--------+---------+---------+---------+
|  13 |     37 |  47.25 |   54.25 |      45 |      37 |
|  14 |     40 |     46 |   47.25 |      47 |   47.75 |
+-----+--------+--------+---------+---------+---------+