none
Payroll tax calculation RRS feed

  • Question

  •    
    Please someone help me in doing the following Payroll tax calculation. 
    What I need is a VBA code for MS Access 2013.
    I am a complete novice and would appreciate if the code is copy and paste.
    Thanks in advance for any help or suggestion.
    SALARY RANGE  TAX RATE  EXEMPTIONS
     $0 - $600  0% 0
     $601 - $1650  10% 150
     $1651 - $3200  15% 200
     $3201 - $5250  20% 350
    $5251 - $7800 25% 500
     $7801 - $10900  30% 1000
     $10901 - ABOVE  35% 1500

     

    Wednesday, February 8, 2017 8:44 AM

Answers

  • Hi,

    You could use Select...Case Statement (Visual Basic) to calculate how may tax should be, then return the result.

    E.g.

    A form with salary field and cal field. Use the following code to update the cal field.

    Private Sub salary_AfterUpdate()
    Dim sal As Integer
    Dim tax As Integer
    sal = salary.Value
    Select Case sal
    Case 0 To 600
    tax = 0
    Case 601 To 1650
    tax = (sal - 600) * 0.1
    Case 1651 To 3200
    tax = (sal - 1650) * 0.15 + 150
    End Select
    cal.Value = sal - tax
    End Sub

    Regards,

    Celeste



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Fisherboy Monday, February 13, 2017 12:30 PM
    Friday, February 10, 2017 8:15 AM
    Moderator

All replies

  • Can you post more details?

    Normally I would use a table to define the salaray range and the tax rate and exemption. Then you can use for example DLookup() to get the necessary value(s).

    Wednesday, February 8, 2017 9:05 AM
  • Hi,

    What is your expected result?

    I would suggest you add a calculated field to calculate expressions.

    Please visit Add a calculated field to a table  & Create an expression 

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, February 9, 2017 4:48 AM
    Moderator
  • Thanks so much for your response. If possible what I was trying to do is since salary info is there when I enter hours worked access to calculate and give me net pay.

    I appreciate your help.

    Thursday, February 9, 2017 8:17 AM
  • Thank you Celeste for your reply. If possible what I was trying to do is since salary info is there when I enter hours worked access to calculate and give me net pay.

    I appreciate your help.

    Thursday, February 9, 2017 8:19 AM
  • Hi,

    You could use Select...Case Statement (Visual Basic) to calculate how may tax should be, then return the result.

    E.g.

    A form with salary field and cal field. Use the following code to update the cal field.

    Private Sub salary_AfterUpdate()
    Dim sal As Integer
    Dim tax As Integer
    sal = salary.Value
    Select Case sal
    Case 0 To 600
    tax = 0
    Case 601 To 1650
    tax = (sal - 600) * 0.1
    Case 1651 To 3200
    tax = (sal - 1650) * 0.15 + 150
    End Select
    cal.Value = sal - tax
    End Sub

    Regards,

    Celeste



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Fisherboy Monday, February 13, 2017 12:30 PM
    Friday, February 10, 2017 8:15 AM
    Moderator
  • The problem with a VBA approach is that data is being encoded in the object definition.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.

    The correct way to model this is, as Stefan pointed out earlier, to store the values in columns in a table.  You have almost shown the structure for such a table in your original post.  The only difference would be that the lower and upper salary values per tax band would be separate columns in the table, e.g.

    TaxParameters
    ….SalaryFrom
    ….SalaryTo
    ….TaxRate
    ….Exemption

    The tax due can then be computed in a query along these lines:

    SELECT EmployeeID, GrossSalary,
    (GrossSalary-Exemption)*TaxRate AS TaxDue,
    FROM PayRoll INNER JOIN TaxParameters
    ON (GrossSalary BETWEEN SalaryFrom AND SalaryTo);

    PS:  The tax rates should be stored as fractional values, i,e 0.1, 0.15, 0.2 etc., and only formatted as percentages.


    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Friday, February 10, 2017 11:24 PM Postscript added.
    Friday, February 10, 2017 11:20 PM
  • I agree with Ken S that this should be approached with a query rather than a VBA code set.  And like Stephan posted - need more info.  

    The max & min of your salary range need to be columns, not a text string in a single field.

    I don't get how the exemptions and hours would all calculate together.  If you gave a couple examples of your calculation I don't think that query would be too difficult.

    Saturday, February 11, 2017 4:39 PM
  • Celeste thanks so much that is exactly what I want. You are a life saver. Thanks so so much.

    Best regards,

    Monday, February 13, 2017 12:31 PM
  • Thanks for your reply. What I was trying to do is that since the salary data , the tax rate and the exemptions there by entering hours worked to get the net pay was my plan.

    EmployeeID   Hours   Salary   TaxRate    Exmptions   NetPay

    Monday, February 13, 2017 12:40 PM
  • Thanks Ken appreciate your response.
    Monday, February 13, 2017 12:40 PM