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```
 ```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```