Introduction

Companies tend to use two broad ways of calculating overtime. Sometimes this may depend on the type of job and the number of shifts considered. In some jobs, people work 9 to 5, Monday to Friday. This is the typical corporate scenario. In some other cases, such as when I worked in a pseudo-health care (mentally challenged) environment, people work 7 days a week and the shifts rotating on a 24-hour a day basis. Regardless, a week worth of work counts for 40 regular hours; anything above that is considered overtime. There is a trick that is also applied, not necessarily because employers tend to cheat (some times some zealous supervisor would think of scoring high points with the management by playing with other people's times...) but because shifts and businesses are different. As mentioned already, there are two typical scenarios.

• First scenario: Suppose a business uses regular shifts from 9 to 5. If an employee works on Monday from 9 to 5 and on Tuesday from 9 to 3. This employee would have worked 8 + 6 = 14 regular hours. Now suppose the employee worked on Monday from 9AM to 7PM, that is 10 hours. At the end of the week, this employee should have worked 8 regular hours and24 overtime hours for the whole week. In other words, this employee would have worked overtime for a particular day only if on that day he worked more than 8 hours
• Second scenario: Suppose a business is run 24 hours a day with long shifts. In this type of business, suppose an employee worked on Monday from 8AM to 8PM, that is 12 hours. At the end of the week, depending on the business, this employee would count for 12 regular hours and no overtime.

In this exercise, we will simulate a payroll calculation. We will consider the first scenario only. We will consider that any time above 8hours for a particular day is overtime. Based on this, an employee who worked only 26 hours in a week can have some overtime while another employee in the same business and who worked 38 hours may not have overtime.

Practical Learning: Simulating a Payroll

1. Start Microsoft Access and create a Blank Database named Payroll1
2. On the main menu, click Insert -> Form
3. In the New Form dialog box, double-click Design View
4. Save the form as Payroll
5. Design it as follows:
    

   Control Caption Name Other Properties Option Group Employee Identification     Text Box Employee Name: txtEmployeeName   Text Box Hourly Salary txtHourlySalary Format: Currency Option Group Time Sheet      Label Monday      Label Tuesday      Label Wednesday      Label Thursday       Label Friday      Label Saturday      Label Sunday      Label First Week:      Text Box   txtMonday1   Text Box   txtTuesday1   Text Box   txtWednesday1   Text Box   txtThursday1   Text Box   txtFriday1   Text Box   txtSaturday1   Text Box   txtSunday1   Text Box   txtMonday2   Text Box    txtTuesday2   Text Box   txtWednesday2   Text Box   txtThursday2   Text Box   txtFriday2   Text Box   txtSaturday2   Text Box   txtSunday2   Option Group Payroll Processing     Button Process It cmdProcessIt    Label Hours       Label Amount       Label Regular     Text Box   txtRegularHours   Text Box   txtRegularAmount Format: Currency Text Box Net Pay: txtNetPay Format: Currency Label Overtime     Text Box   txtOvertimeHours   Text Box   txtOvertimeAmount Format: Currency Button Close cmdClose

6. Right-click the Process It button and click Build Event...
7. In the Choose Builder dialog box, double-click Code Builder
8. Implement the event as follows:
    

   Private Sub cmdProcessIt_Click() Dim monday1 As Double Dim tuesday1 As Double Dim wednesday1 As Double Dim thursday1 As Double Dim friday1 As Double Dim saturday1 As Double Dim sunday1 As Double Dim monday2 As Double Dim tuesday2 As Double Dim wednesday2 As Double Dim thursday2 As Double Dim friday2 As Double Dim saturday2 As Double Dim sunday2 As Double Dim totalHoursWeek1 As Double Dim totalHoursWeek2 As Double Dim regHours1 As Double Dim regHours2 As Double Dim ovtHours1 As Double Dim ovtHours2 As Double Dim regAmount1 As Currency Dim regAmount2 As Currency Dim ovtAmount1 As Currency Dim ovtAmount2 As Currency Dim regularHours As Double Dim overtimeHours As Double Dim regularAmount As Currency Dim overtimeAmount As Currency Dim totalEarnings As Currency Dim hourlySalary As Currency ' Retrieve the hourly salary hourlySalary = CDbl(Me.txtHourlySalary) ' Retrieve the time for each day ' First Week monday1 = CDbl(Me.txtMonday1) tuesday1 = CDbl(Me.txtTuesday1) wednesday1 = CDbl(Me.txtWednesday1) thursday1 = CDbl(Me.txtThursday1) friday1 = CDbl(Me.txtFriday1) saturday1 = CDbl(Me.txtSaturday1) sunday1 = CDbl(Me.txtSunday1) ' Second Week monday2 = CDbl(Me.txtMonday2) tuesday2 = CDbl(Me.txtTuesday2) wednesday2 = CDbl(Me.txtWednesday2) thursday2 = CDbl(Me.txtThursday2) friday2 = CDbl(Me.txtFriday2) saturday2 = CDbl(Me.txtSaturday2) sunday2 = CDbl(Me.txtSunday2) ' Calculate the total number of hours for each week totalHoursWeek1 = monday1 + tuesday1 + wednesday1 + thursday1 + _ friday1 + saturday1 + sunday1 totalHoursWeek2 = monday2 + tuesday2 + wednesday2 + thursday2 + _ friday2 + saturday2 + sunday2 ' The overtime is paid time and half Dim ovtSalary As Double ovtSalary = hourlySalary * 1.5 ' If the employee worked under 40 hours, there is no overtime If totalHoursWeek1 < 40 Then regHours1 = totalHoursWeek1 regAmount1 = hourlySalary * regHours1 ovtHours1 = 0 ovtAmount1 = 0 ' If the employee worked over 40 hours, calculate the overtime ElseIf totalHoursWeek1 >= 40 Then regHours1 = 40 regAmount1 = hourlySalary * 40 ovtHours1 = totalHoursWeek1 - 40 ovtAmount1 = ovtHours1 * ovtSalary End If If totalHoursWeek2 < 40 Then regHours2 = totalHoursWeek2 regAmount2 = hourlySalary * regHours2 ovtHours2 = 0 ovtAmount2 = 0 ElseIf totalHoursWeek2 >= 40 Then regHours2 = 40 regAmount2 = hourlySalary * 40 ovtHours2 = totalHoursWeek2 - 40 ovtAmount2 = ovtHours2 * ovtSalary End If regularHours = regHours1 + regHours2 overtimeHours = ovtHours1 + ovtHours2 regularAmount = regAmount1 + regAmount2 overtimeAmount = ovtAmount1 + ovtAmount2 totalEarnings = regularAmount + overtimeAmount Me.txtRegularHours = regularHours Me.txtOvertimeHours = overtimeHours Me.txtRegularAmount = CCur(regularAmount) Me.txtOvertimeAmount = CCur(overtimeAmount) Me.txtNetPay = CCur(totalEarnings) End Sub

9. Return to the form
10. Right-click the Close button and click Build Event...
11. In the Choose Builder dialog box, double-click Code Builder
12. Implement the event as follows:
     

    Private Sub cmdClose_Click() On Error GoTo Err_cmdClose_Click DoCmd.Close Exit_cmdClose_Click: Exit Sub Err_cmdClose_Click: MsgBox Err.Description Resume Exit_cmdClose_Click End Sub

13. Return to the form and switch it to Form View
14. Test it with some values