Microsoft Access Examples - Payroll Simulation

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