Code for calculating employees overtime pay

[Nixx1401]

I have an access database I was asked to modify to have the calculations generate correctly. I did not create the database and the person who did is no longer working at the institution. Here goes

The database calculates deductions for overtime pay producing the overall time pay.

NIS: National Insurance Scheme
PAYE: Pay as you earn

A temporary employee is charged 10.1% NIS on gross pay
An appointed employee is 8.8%
Maximum salary for which NIS is charged is $4090.00 so therefore anyone earning more than $4090.00 is not charged NIS

PAYE is charged to salaries over $4100.00 at a rate of 20% and above $4100.00 35%

Below is the code which is in the database but the calculations are incorrect.

A copy of the database is also attached the calculation is found in the pay transaction table under the hour event procedure.

Private Sub hours_AfterUpdate()
'Calculates Pay For Employee

Dim rsPay As Recordset, strSQl As String
Dim rsSettings As Recordset, TotalPay As Currency, nPay As Currency
Dim qDef As QueryDef, nTaxable As Currency, nMonthLim As Currency
Dim nSalary As Currency
Set rsSettings = CurrentDb.OpenRecordset("Payroll Settings", dbOpenDynaset)
Set qDef = CurrentDb.QueryDefs("Salary Info Query")
qDef.Parameters(0) = Me.natregno
Set rsPay = qDef.OpenRecordset
If Not rsPay.EOF Then
Me.pay = Me.hours * Nz(rsPay("rate"), 1)
TotalPay = Nz(rsPay("Allowance"), 0) + Nz(rsPay("salary"), 0) + Nz(Me.pay, 0)
nPay = Nz(rsPay("Allowance"), 0) + Nz(rsPay("salary"), 0)
If nPay >= rsSettings("NISLimit") Then
Me.nis = 0
Else
nPay = Abs(rsSettings("NISlimit") - rsPay("salary"))
If nPay > Me.pay Then
Me.nis = Me.pay * IIf(rsPay("emp_code") = "E", rsSettings("nisrateP"), rsSettings("nisrateT"))
Else
Me.nis = nPay * IIf(rsPay("emp_code") = "E", rsSettings("nisrateP"), rsSettings("nisrateT"))
End If
End If

If ((TotalPay * 12) - rsPay("persallow")) > rsSettings("PAYELimit") Then
nSalary = (((Nz(rsPay("Allowance"), 0) + Nz(rsPay("salary"), 0)) * 12) - rsPay("persallow")) / 12
nMonthLim = rsSettings("PAYELimit") / 12
If nSalary < nMonthLim Then
nTaxable = nMonthLim - nSalary
Me.tax = (nTaxable * rsSettings("paye1")) + ((Me.pay - nTaxable) * rsSettings("paye2"))
Else
Me.tax = Me.pay * rsSettings("paye2")
End If
Else


Me.tax = Me.pay * rsSettings("paye1")
End If
Me.netpay = Me.pay - Me.nis - Me.tax
Else
MsgBox "Officer Number Not Found!!", vbInformation + vbOKOnly, cCaption
End If
Set rsPay = Nothing
Set rsSettings = Nothing
Set qDef = Nothing
Me.Refresh
End Sub
Private Sub natregno_BeforeUpdate(Cancel As Integer)
Dim strSQl As String, db As Database, rs As Recordset, lError As Boolean
lError = False
Set db = CurrentDb
Set rs = db.OpenRecordset("Names", dbOpenDynaset)
rs.FindFirst "emp_id = '" & Trim(Me.natregno) & "'"
If rs.NoMatch Then
MsgBox "National Registration Number Not Found!!", vbInformation + vbOKOnly, cCaption
Cancel = True
lError = True
End If
Set rs = Nothing
db.Close
Set db = Nothing
If lError Then
Exit Sub
End If
Me.RecordsetClone.FindFirst "natregno = '" & Me.natregno & "' AND pay_pd_id = " & _
[Forms]![pay transactions]![cmb_periods]
If Not Me.RecordsetClone.NoMatch Then
MsgBox "This officer already has hours entered for this period.", vbInformation + vbOKOnly, cCaption
Cancel = True
End If
End Sub

Public Sub Calc_Pay()

End Sub

[orange]

Do you have any test data?
By that I mean a record or records where you have worked out the calculations and know what the expected results are/should be.

I have opened your mdb (and thanks for using mdb, many of us don't have 2007/2010).
Is there anything I'm suppose to know for using this db?

I found a lot of empty tables??? Is that important?

Is there a way to work thru this by identifying 1 or 2 people and seeing where the observed varies from the expected?

TableName RecordCount

Allowance Types 0
Allowance Types_Old 0
Allowance Types_Old1 6
Allowance Types1 3
Allowances 0
Allowances_old 0
Allowances_old1 19
Allowances1 783
Bank Branches 0
Bank Branches1 51
Bank Groups 0
Bank Groups1 7
Batch 0
Batch1 13
Categories 0
Categories1 6
Emp_Types 0
Emp_Types1 2
Names 0
Names1 405
Pay Periods 0
Pay Periods1 170
Payroll Settings 0
Payroll Settings1 1
Prison Officers 0
Prison Posts 0
Prison Posts1 39
Rates 0
Rates1 7
Salary Scales 0
Salary Scales1 38
Secret 0
Secret1 1
Stations 0
Stations1 1
Switchboard Items 0
Switchboard Items1 19
Switchboard Items2 0
Switchboard Items21 4
Tax Codes 0
Tax Codes1 3
Transactions 0
Transactions-Old 0

[Nixx1401]

I know it has been a while since you asked me about the test data in the database but I overburndened with work and did not get the opportunity to enter the test data I have done so now and have some additional information as well.

With the test data that was entered in the Pay Transactions Form for NatRegNo
5703221467 His salary is P21A which is $3761.52 his gross pay is calculated correct at $429.60 but the NIS and the PAYE is calculated incorrectly.

The NIS calculation should be If the Salary+GrossPay+DutyAllowance is greater than NISLimit which is $4090.00 then NIS is (Salary $3761.52 + Duty Allowance $60.00) - NISLimit $4090.00 = $268.48 * nisrateP which is 8.8% = $23.63

The code for the PAYE was done incorrectly

PAYE is calculated as follows:
IF Salary+GrossPay+Allowances is greater than $4100.00 PAYE is (Salary+Allowances+GrossPay) - NISLImit $4100 * 35% + (Salary+Allowance) - NISLimit $4100 * 20%

Else

IF Salary is less than $4100 PAYE is (Hours*Rate) x 20%

The test data that is entered the following natregno and calculations are correct 5909301111 & 5802111271

I hope you understand the following.

Nixx1401

[Nixx1401]

Please find attached database with data

[orange]

So, I opened your new database. I see a lot of calculations in the Batch_Update routine.

I looked at the Pay Transactions Form and the subform but I don't see any calculations. And I don't see where or how Batch_Update is related to that Form.

So I guess what I'm asking you, "How can I help?? What do I need to know in order to follow the calculations you mention in the post and match them to your working code???

[Nixx1401]

The code that was posted initially is the code to be corrected so the calculations can generate correctly

This code is located in the pay transactions form under the hours text box property.

These are all the conditions

NIS

If salary is greater than $4090 NO NIS is charged. Calculated correct

If salary+GrossPay+DutyAllowance is greater than NISLimit NIS is (Salary+Duty) - NISLimit * nisrateP or nisrateT
If Salary less than NISLimit NIS is (Hours * Rate) * nisrateP or nisrateT

The nisrates is determine by the officer status if there are permanent or temporary this is found in the payroll settings table

The Duty allowance is found in the allowance type which specifies the type of allowance and the allowance table which specifies the money earning for each allowance

NISLimit is found in the payroll settings table


PAYE

If Salary+GrossPay+Allowances is greater than PAYELimit. PAYE is (Salary+Allowance+Grosspay) - PAYELimit* paye2 + (Salary+Allowance) - PAYELimit * paye1

If Salary is less than PAYELimit. PAYE is (Hours * Rate) * paye1. Calculated correctly

PayeLimit, paye1 & paye2 is found in the payrolls settings table

I would like help modifying the code under the hours textbox to ensure the calculations are calculated correctly

[orange]

Have you considered creating specific functions for NIS Calculation and PAYE calculation rather than a series of inline calculations? It may not do anything but separate the calculations.
Am I suppose to be looking at natregno 5703221467?
I have some other things to do today, so won't be able to look further until later.

In earlier post you said NIS and PAYE were calculated incorrectly. Perhaps, I missed it, but did you highlight what these amounts should be?

What do you mean by If Salaray < NISLimit then nis =(Hours *Rate) *10.10% or 8.8%? How do you identify which is to be used? OK I think I found it ------Iif (emp_code = "E",.088,.101)
So for natregno 5703221467 I get nis to be 37.8048

What is DutyAllowance vs Allowances?

Is this statement true?

TotalPay = Nz(rsPay("Allowance"), 0) + Nz(rsPay("salary"), 0) + Nz(Me.pay, 0)

[Nixx1401]

Allowances has 3 categories Duty, Washing n Driving only drivers get driving all other staff recdives the other 2

NIS

Salary 3761.52
Duty + 60.00
3821.52

NISLimit 4090.00
- 3821.52
268.48

nisrateP 8.8% * 268.48 = 23.63

PAYE

Salary 3761.52
Duty 60.00
Washing 114.43
Gross 429.60
4365.55

4365.55
PAYELimit 4100.00
265.55

paye1 35% * 265.55 = 92.94

Salary 3761.52
Duty 60.00
Washing 114.43
3935.95

PAYELimit 4100.00
3935.95
164.05

paye2 20% * 164.05 = 32.81

PAYE due 32.81+ 92.94 = 125.75

incorrect statement

[orange]

You say that the statement is incorrect. Well that statement is taken from the hours_AfterUpdate event. I did not understand the statement, so that is why I asked.
If the statement is incorrect, then I would suggest all the calculations in hours_AfterUpdate are suspect as well.

Also, the query "Salary Info Query" which is the source data for your Transactions subform, is showing 80.00 for your Allowance, not 60.00. This Allowance value of 80.00 is also used in the calculation

nPay = Nz(rsPay("Allowance"), 0) + Nz(rsPay("salary"), 0)

It also seems that nPay is used for different things in that procedure.

In your last post I don't see where you have used
If Salary less than NISLimit NIS is (Hours * Rate) * nisrateP or nisrateT which you showed previously.

Sorry, but I am confused as to the appropriate fields and calculations.

In my opinion, in order to make progress, you need to take a specific condition with
- a specific individual, with a known duty allowance, salary etc. and have a separately calculated set of NIS and PAYE values that reflect the "company financial policies" and then work those through the programs you have. Comparing the observed values from the queries and programs to the "known good values" from the separate calculations. You may need a few "test cases" to pick up the various conditions.

For maintenance of the programs you have, it would be beneficial to identify each variable and field and document what it means exactly.

eg TotalAll - is the Sum of all allowances for an employee
allowance (in Rates table) is a currency value associated with a rank_id???
allowance in (Name table) is a persallow related to an Exempt_id in the Tax Codes table.

Sorry if this sounds negative, it is meant to be constructive, but I feel we are trying to hit a moving target. A target that is not clearly defined.

[Nixx1401]

I recognised there is a flaw in the example calculations given to me from the accounts dept will forward the necessary answers to the questions you have on tuesday. Monday is a national holiday here in Barbados

[Nixx1401]

I hope to clear up all of your questions with the below information

This database was created by a employee who no longer works for the institution and I am trying to correct the flaws. I not an expert on access i know the basics and can understand code to an extent.

The after update event of the hours is not incorrect in its entirety. Some of the calculations are correct.

I will list all the conditions and indicated which ones are correct. Some changes were made to the conditions

NIS

1. If Salary > $4090 (NISLimit) No NIS is charged. Correct

2. IF Salary+GrossPay+DutyAllowance > $4090 NIS is (Salary+DutyAllowance) - $4090 * 10.10% or 8.80% Incorrect

Else

3. (Hours * Rate) 10.10% * 8.8%

PAYE

4. IF Salary+Allowances+GrossPay < $4100 PAYE is (Hours * Rate) * 20% Correct

Else

5. (Salary+Allowances+GrossPay) - $4100 * 35% + (Salary+Allowances) - $4100 * 20% Incorrect

6. IF Salary > $4100 PAYE (Hours * Rate ) * 35% Correct

$4090 - NISLimit - Payroll settings Table
8.8% - nisrateT - Payroll settings Table
10.10% - nisrateP - Payroll settings Table
GrossPay - PayTransaction Form

I will indicate where the test data ref the conditions

natregno 5409120011 eg Condition #1 & #6 Calcuation is correct

natregno 5703221467 eg Condition #2 & #5 Calculation incorrect

natregno 5802111271 eg Condition #1 & #6 Calculation correct

natregno 5909301111 eg Condition #3 & #4 Calculation correct

The fields and calculations are as follows

natregno5409120011 calculation

Salary $4651.30

Rate $31.74
Hours x 5
GrossPay 158.70

NO NIS Charged salary > 4090

PAYE $158.70 x 35% = $55.55

natregno5703221467

Salary $3761.52

Rate $ 26.85
Hours x 16
GrossPay 429.60

NIS = Salary 3761.52
Duty 80.00
Washing 114.43

[orange]

Nixx1401,
Did you make any headway on the different fields all identified in some way with Allowance?
I'm looking at your condition 2 and 5.

Why would someone get to include Duty Allowance but not Washing Allowance???

Employee natregno5703221467

You show

Salary $3761.52
 
Rate $ 26.85
Hours x 16
GrossPay 429.60
 
NIS = Salary 3761.52
Duty 80.00
Washing 114.43

But in the Allowances table for this employee, it has
Allowances
natregno allowance description
5703221467 $60.00 1
5703221467 $114.43 2
1---duty
2---washing There is no $80.00


Also, your term Grosspay is really GrossOvertimePay---Is this correct? I ask this because the expression
Salary +DutyAllowance+GrossPay didn't make sense, but
BaseSalary +Duty Allowance +GrossOvertimePay would represent some earnings before Taxes/deductions.


Please explain why DutyAllowance is 80 and not 60.

I have a feeling that the different uses of Allowance is causing issues with calculations and incorrect values.
Which allowance is to be used where? What do the different allowance fields mean? Why aren't they properly defined and named?

[Nixx1401]

First that reply was incomplete I took a break to go through the code before I sent you a reply anyway

Allowances

Any employee is given allowances monthtly these allowances are Duty Allowance, Washing Allowance, Driving Allowance. Only drivers are entitled to Driving Allowance.

Duty Allowance is allocated according to rank of an employee therefore a person who is an Prison Officer II will receive duty allowance of $60.00 and Prison Officer I, Orderly Officer, Officer Instructor, Chief Officer and Assistant Chief Officer will receive $80.00.

An employee rank is entered in the Officer Details Form and the relevant duty allowance is allocated. This data is stored in the Rates Table.

For NIS calculations only the duty allowance is taxable and not the washing allowance.

Please forgive me when I was entering the test data I accidently entered $60.00 Duty Allowance for natregno 5703221467 He is classified as a Prison Officer I and therefore is duty allowance should be $80.00

GrossPay yes refers to GrossOverTimePay I used GrossPay because this is the name used in the PayTransactions Form

I spent some time today going throug the code and recognise that although some of the calculations are correct I will have to rewrite some of the code.


npay = Allowance + Salary
Me.pay = Hours * Rate (this calculates the GrossOvertime Pay)
TotalPay = Allowance + Salary + Me.pay
npay = Allowance + Salary
These are calculations which can be used. Please forgive previously for indicating that the TotalPay was incorrect. I was using my cellphone

I am going to resend the calculations all of the calculations wasn't sent.

natregno5409120011 calculation

Salary $4651.30

Rate $31.74
Hours x 5
GrossPay 158.70

NO NIS Charged salary > 4090

PAYE $158.70 x 35% = $55.55

natregno5703221467

Salary $3761.52

Rate $ 26.85
Hours x 16
GrossPay 429.60

NIS

Salary 3761.52
Duty 80.00
Total 3841.52

NISLimit 4090.00
- 3841.52
248.48

NIS Payable 248.48x 8.8% = 21.87

PAYE

Salary 3761.52
Duty 80.00
Washing 114.43
Gross Pay 429.60
Total 4385.55

Total 4385.55
PAYELimit 4100.00
285.55

285.55 % 35% = 99.94

Salary 3761.52
Duty 80.00
Washing 114.43
Total 3955.95

PAYELimit 4100.00
Total 3955.95
144.05

144.05 x 20% = 28.81

PAYE Payable = 28.81+99.94 = 128.75

natregno5909301111

Salary 2298.84 (Salary < NISLimit)

Rate 24.41
Hours x 7
Total 170.87

NIS Payable = 170.87 * 10.10% = 17.26

PAYE Payable = 170.87 * 20% = 34.17

Are you referring to documentation for the entire database or for the code only

Me.pay = Calculates the Gross Pay
TotalPay = GrossPay+Allowances+Salary
nPay = Allowances+Salary
Gross Pay = Hours * Rate
Rate = Varies according to the rank of the employee in the Rates Table
NISLimit = Maximum Limit for which NIS is charged
PAYELimit = Maximum Limit for which PAYE is charged

Please indicated if this is what you are asking for or if I should review the above documentation

Basically if I have to change the entire code to reflect the conditions above I am willing to with little assistance.

I feel like I am not explaining myself thoroughly. Please help me

[orange]

Nixx1401,

Please check the Allowances table and the Rates Table!!!
You have 2 different values for allowance.
Duty Allowance is in Allowances as are Washing and Driving Allowance.

What is the Allowance in Rates table???????

If you sit with Finance people What do they calculate the NIS and PAYE for
natregno5703221467 ?????

I'm trying to help, but we have to determine what is the real data to use with expressions. And we have to know how Finance/Pay and Benefits determines the various amounts.

[Nixx1401]

The Allowances table records the entries entered for the all 3 allowances types for each employee.
Duty Allowance = $60.00 or $80.00 according to rank
Washing Allowance = $114.43
Driving Allowance = $73.22

Rates Table
Displays the rank of an employee, hourly rate for overtime and duty allowance given according to each rank. eg Officer Instructor get $26.85 an hour for overtime and receives $80.00 duty allowance.

The Allowance in the Rates Table is Duty Allowance for each rank

PAYE for 5703221467 is calculated as followed

Salary $3761.52 + Duty Allowance $80.00 + Washing Allowance $114.43 + Gross Overtime Pay $429.60= $4385.55

$4385.55- $4100 PAYELimit = $285.55

$285.55* 35% paye2 = $99.94

Salary 3761.52 + Duty Allowance $80.00 + Washing Allowance $114.43 = $3955.95

PAYELimit $4100 - $3955.55= 144.05

144.05* 20% paye1 = $28.81

PAYE Payable = $28.81 + $99.94= $128.75
NIS for 5703221467

Salary 3761.52 + Duty Allowance $80.00 = $3841.52

$3841.52 - NISLimit $4090.00 = $248.48

NIS Payable $248.48 * 8.80% = $21.87

These are the calculations for the NIS and PAYE

I hope this answers your queries