I have looked at your info and have tried to work through NIS and PAYE calculations.
I focused on natregno 5703221467.
I have the code to get the NIS and Paye
Code:
For NatRegNo (JNIS)5703221467 Emp_Code E Calculated NIS is ... 21.87
Calculated PAYE is ... 128.75
But as I got into the Transaction SubForm I realized that, although you and I were discussing PAYE, the subform is dealing with Tax. The PAYE column heading is using the Tax variable. It also deals with monthly rates etc which we hadn't discussed.
In my tests, I set up functions for NIS and PAYE calculations. The functions are JNIS and JPaye.
These functions are
Code:
'---------------------------------------------------------------------------------------
' Procedure : JNis
' Author : Jack
' Created : 4/30/2011
' Purpose : Calculations based on posts. NatRegNo 5703221467.
' Used this function to separate any extraneous info
' NOTE: There may be other conditions involved!!!!!!!
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'------------------------------------------------------------------------------
'
Function JNis( _
strNatRegNo As String _
, CurSalary As Currency _
, curDutyAllowance As Currency _
, curNISLimit As Currency _
, NumHours As Currency _
, curRate As Currency _
, emp_code As String _
, NISrateP As Currency _
, NISRateT As Currency) As Currency
'Note:
'DutyAllowance is Allowance from Rates Table for this Employee's rank
On Error GoTo JNis_Error
Dim curnis As Currency
' no NIS if salary >=NisLimit
If CurSalary >= curNISLimit Then
curnis = 0
End If
'
If CurSalary + curDutyAllowance < curNISLimit Then
curnis = (curNISLimit - (CurSalary + curDutyAllowance)) * IIf(emp_code = "E", 0.088, 0.101)
End If
'Set the return value to 2 decimal places using Round function
JNis = Round(curnis, 2)
Debug.Print "For NatRegNo (JNIS)" & strNatRegNo & " Emp_Code " & emp_code; " Calculated NIS is ... " & JNis
On Error GoTo 0
Exit Function
JNis_Error:
MsgBox "Error " & Err.Number & " (" & Err.description & ") in procedure JNis of Module Module1"
End Function
'---------------------------------------------------------------------------------------
' Procedure : jPaye
' Author : Jack
' Created : 4/30/2011
' Purpose : Attempt to calculate PAYE based on posts on AccessForums.net May 4/2011
'
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'------------------------------------------------------------------------------
'
Function jPaye(curPayeLimit As Currency, _
strNatRegNo As String, _
CurSalary As Currency, _
NumHours As Currency, _
curRate As Currency, _
CurAllowances As Currency, _
Paye1 As Currency, _
Paye2 As Currency) As Currency
'Note CurAllowances is Sum of allowances in Allowance Table for this employee
Dim CurGrossOvertimePay As Currency
Dim curPayeTotal As Currency
Dim curPaye1 As Currency
Dim curPaye2 As Currency
'Dim Paye1 As Currency
'Dim Paye2 As Currency
On Error GoTo jPaye_Error
CurAllowances = 80 + 114.43 'Duty allowance +Washing allowance
CurGrossOvertimePay = NumHours * curRate
If (CurSalary + CurGrossOvertimePay + CurAllowances) >= curPayeLimit Then
curPaye2 = ((CurSalary + CurGrossOvertimePay + CurAllowances) - (curPayeLimit)) * Paye2
curPaye1 = ((curPayeLimit - (CurSalary + CurAllowances)) * Paye1)
curPayeTotal = Round(curPaye1 + curPaye2, 2)
Else
'
' it is not clear what the true expression is for this else condition
curPayeTotal = CurGrossOvertimePay * Paye1
End If
Debug.Print "Calculated PAYE is ... " & curPayeTotal
On Error GoTo 0
Exit Function
jPaye_Error:
MsgBox "Error " & Err.Number & " (" & Err.description & ") in procedure jPaye of Module Module1"
End Function
I don't think all conditions and expressions are represented. I got confused again when I found the Transaction subform afterUpdate code was dealing with Tax and annual/month terms and not PAYE as had been discussed.
The outputs (from my Functions) for the other natRegNo was as follows
For NatRegNo (JNIS)5409120011 Emp_Code E Calculated NIS is ... 0
Calculated PAYE is ... 167.4
For NatRegNo (JNIS)5703221467 Emp_Code E Calculated NIS is ... 21.87
Calculated PAYE is ... 128.75
For NatRegNo (JNIS)5802111271 Emp_Code E Calculated NIS is ... 0
Calculated PAYE is ... 121.14
For NatRegNo (JNIS)5909301111 Emp_Code T Calculated NIS is ... 174.85
Calculated PAYE is ... 34.174
For NatRegNo (JNIS)5811075001 Emp_Code E Calculated NIS is ... 76.83
Calculated PAYE is ... 363.97
I hope some of this is helpful.
A caution for you is that the Allowances table has Duty and Washing Allowances.
The Rate table also has Duty Allowance. This is confusing and will be a maintenance headache.
NIS calculations involve Rate table, where as PAYE uses the Allowances Table (TotalAll) in the Salary Info Query.
As stated previously, the Transaction subform has a heading PAYE (which we have focused on), but the underlying data is based on tax calculations (me.tax etc) in the code behind the form.