computation of calculated field in query based on conditions

[anyaley]

I want to compute taxes on salaries in a test access payroll query.

Conditions are as follows:

salary is a calculated field in the query and is monthly.
It must be converted to annual-salary(salary*12) before computation based on
below conditions.
The end tax result must be recoverted to monthly(annual tax/12).



if annual-salary < 250.00, no tax.

Between 250.00 and 500.00=5% of excess over 250 plus 10.00

Between 501.00 and 1000.00=10% of excess over 500 plus 25.00

Between 1001.00 and 1500.00=15% of excess over 1000 plus 20.00

Between 1500.00 and 2000.00=20% of excess over 1500 plus 25.00

2000.00 = 30% plus 35.00.

Where and how can I get this formula in my query?

Please help

Alex A Nyaley, Monrovia , Liberia , West Africa .

 

[raskew via AccessMonster.com]

Hi -

Since you are working with even brackets (multiples of 500) that shouldn't be
too difficult. The following example uses the Mod function to categorize
annual salaries, i.e.
0 - 499 = Cat 1. 500 - 999 = Cat 2'; 1000 - 1499 = Cat 3; 1500 - 1999 =Cat 4

Then, using the Switch() function to apply the correct formula based on
Category.

SELECT
tblSalaries.EmployeeID
, tblSalaries.Sal
, Int([sal]/500)+IIf([sal] Mod 500>0,1) AS Cat
, Switch([cat]=1,(0.05*([sal] Mod 250)+10)/12,
[cat]=2,(0.1*([sal] Mod 500)+25)/12,
[cat]=3,(0.15*([sal] Mod 1000)+20)/12,
[cat]=4,(0.2*([sal] Mod 1500)+20)/12,
True,(0.3*([sal] Mod 2000+35)))/12 AS Tax
FROM
tblSalaries
WHERE
(((tblSalaries.Sal)>=250))
ORDER BY
tblSalaries.Sal;

Lookup both the Mod() and Switch() functions for more detailed explanation.

HTH - Bob

 

[Marshall Barton]

I want to compute taxes on salaries in a test access payroll query.

Conditions are as follows:

salary is a calculated field in the query and is monthly.
It must be converted to annual-salary(salary*12) before computation based on
below conditions.
The end tax result must be recoverted to monthly(annual tax/12).



if annual-salary < 250.00, no tax.

Between 250.00 and 500.00=5% of excess over 250 plus 10.00

Between 501.00 and 1000.00=10% of excess over 500 plus 25.00

Between 1001.00 and 1500.00=15% of excess over 1000 plus 20.00

Between 1500.00 and 2000.00=20% of excess over 1500 plus 25.00

While it may be possible to put together such a complocated
expression, it would be far better to place that taxing
information in another table (named TaxRates):

Low High Base Rate
0 250 0 0
250 500 10 .05
500 1000 25 .10
1000 1500 20 .15
1500 2000 25 .20
2000 9E9 35 .30

With that in place, your query would be something like:

SELECT person,
(some expression) As MonthSalary,
12 * MonthSalary As YearSalary,
(Low + Rate * (YearSalary - Low) / 12 As MonthTax
FROM Payroll INNER JOIN TaxRates
ON 12 * (some expression) >= Low
AND 12 * (some expression) < High

A major benefit of this kind of approach is that you do not
have to track down and modify the query calculations when
the numbers change. Instead you can just change the values
in the table and be done with it.

 

[taurus via AccessMonster.com]

As this is a progressive tax regime, you might start with a table to hold the
tax ranges like tblTax (from, to, rate, addenum). Assuming the table holding
employee salary is like tblPayroll (employeeId, data, salary), then a query
like qryPayrollTaxes:

SELECT tblPayroll.employeeId, Sum(tblPayroll.salary) AS ytdSalary, Sum(
[salary])*[rate]+[addenum] AS ytdTax, tblTax.rate, tblTax.addenum, tblTax.
from, tblTax.to
FROM tblPayroll, tblTax
GROUP BY tblPayroll.employeeId, tblTax.rate, tblTax.addenum, tblTax.from,
tblTax.to, tblTax.rate
HAVING (((tblTax.from)<Sum([Salary])) AND ((tblTax.to)>Sum([Salary])));

might help. This would still need to compare with previous year to date
computation to obtain the current month tax.