Calculate FICA for Employees

[reneerita]

I have, using tables and queries, created a report that calculates gross wages of employees. It also does the calculations for SS and Medicare deductions. I also have the federal income tax tables as tables with fields for Minimum Wages, Maximum Wages, and Total Tax Owed. I want to link Employees gross wages to the appropriate tax table and have it find the total tax owed for that employee within the Min and Max range.

Basically, I want a statement that does this:

If [Employees]![Allowances]=0&[Employees]![Status]="Single"or"Married - at Higher Single Rate", Sum(([Weekly Pay 1]![Weekly Regular Pay]+[Weekly Pay 1]![Weekly Overtime Pay])+Sum((Weekly Pay 2]![Weekly Regular Pay]+[Weekly Pay 2]![Weekly Overtime Pay]) ....



Is used on table [Tax Table Single 0] fields [Minimum Wages] and [Maximum Wages] to find which row to = the third field of [Total Tax Owed].

Tax Table Single 0 has 3 fields: Minimum Wages, Maximum Wages, and Total tax Owed. I want the Gross Wages I generated using the sums mentioned above to also pull from the linked employee's Status (single, married - at higher single rate, married) and Allowances (so pull from the right table: ex. Tax Table Single 0, Tax Table Married 2, etc.) to find the right row in the range between the two fields to give me the right row of the Total Tax Owed field. Can anyone help?

[June7]

A DLookup() might be the easiest approach.

DLookup("[Total Tax Owed]", "tablename", [GrossWages] & " BETWEEN [Minimum Wages] AND [Maximum Wages]")

Setting the tablename reference is complicated by having multiple tax tables. A normalized structure would be a single table with fields for status and allowance. A UNION query could reorganize the multiple tax tables into a single dataset.

However, based on the tax table names you referenced, perhaps:

DLookup("[Total Tax Owed]", "[Tax Table " & [Status] & " " & [Allowance] & "]", [GrossWages] & " BETWEEN [Minimum Wages] AND [Maximum Wages]")


Instead of reinventing the wheel I would and have used OTS software for accounting, including payroll. QuickBooks is a robust and economical option.

[reneerita]

Thank you. I will check as soon as I get back in on Monday. I actually do currently have the Tax Table set up as only 2 tables. One has the Max, Min, Allowances, and Tax Owed for single and the other is the same for married. My Employee table has a field for number of allowances and a lookup field with 3 options: Married, single, and Married at higher single rate. I was just confused how I would like my employee table number of allowances with the tax table and pull only from the table for married vs single. Then, using the gross wages calculated through a series of queries and combination with tables for hours, hourly wages, etc. I thought it might be easier if I created 11 tables for single and 11 for married based on 0-10 allowances. Is there a way to take my present set up and link it to a single table with the four fields I have now and add a field for married or single?

[ssanfu]

I would agree with June. Using off the shelf (OTS) software would be easiest.

I have, using tables and queries, created a report that calculates gross wages of employees. It also does the calculations for SS and Medicare deductions. I also have the federal income tax tables as tables with fields for Minimum Wages, Maximum Wages, and Total Tax Owed. I want to link Employees gross wages to the appropriate tax table and have it find the total tax owed for that employee within the Min and Max range.

Basically, I want a statement that does this:

If [Employees]![Allowances]=0&[Employees]![Status]="Single"or"Married - at Higher Single Rate", Sum(([Weekly Pay 1]![Weekly Regular Pay]+[Weekly Pay 1]![Weekly Overtime Pay])+Sum((Weekly Pay 2]![Weekly Regular Pay]+[Weekly Pay 2]![Weekly Overtime Pay]) ....

You really have different tables for each week???


I would also have a field on the Fed tax table for the tax year.
Unless you intend to have a different tax table for each year? Then you would have to edit the calculations each year.....

[June7]

I don't think can establish a link based on a range. The DLookup will have to be adjusted for however many tax tables. The WHERE ARGUMENT criteria will have to be expanded. For two tables remove Allowance from the dynamic tablename construct. For one table the tablename will be static and will need filter criteria for Status and Allowance.

However, I just realized the Status is not as straightforward as I thought. You have 3 Status and 2 tables.

DLookup("[Total Tax Owed]", "[Tax Table " & IIf([Status] LIKE "*Single*", "Single", "Married") & "]", [GrossWages] & " BETWEEN [Minimum Wages] AND [Maximum Wages] AND Allowance=" & [Allowance])