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

 

 

 

Home Copyright 2004 FunctionX, Inc.