tax computation in Access query

[anyaley via AccessMonster.com]

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

Conditions are as follows:

Grosspay 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).
Below is the Tax Table(tbltx).
FROM = Begining range of Gross pay(begrng)
TO = End range of gross pay(endrng)
RATE = Rate applied on excess over beginning range(rate)
ADD = Additional amount after applying rate(addamt).
Example:
Gross pay= 1,500
Tax= 1500*12=18000(category 2)

(18000-12001)=5999
(5999*5%)=299.95
(299.95+240)=539.95(annual tax)
(539.15/12)=44.99(monthly tax)

Tax = 44.99(tax this month)

FROM TO RATE% ADD AMT
1 1.00 12,000 2 0.00
2 12,001 50,000 5 240
3 50,001 100,000 10 2,140
4 100,001 200,000 15 7,140
5 200,001 400,000 20 22,140
6 400,001 800,000 25 62,140
7 800,001 1,200,000 30 162,140
8 1,200,001 999999999 35 282,140

Between 1.00 and 12000=2% .
12,001 to 50,000=5% of excess over 12001 plus 240
50,001 to 1000,000=10% of excess over 50,001 plus 2,140
100,001 to 200,000=15% of excess over 100,001 plus 7,140
200,001 to 400,000=20% of excess over 200,001 plus 22,140
400,001 to 800,000=25% of excess over 400,001 plus 62,140
800,001 to 1,200,000=30% of excess over 800,001 plus 162,140
1,200,001 to 999999999 35% of excess over 1,200,001 plus 282,
140

 

[Michel Walsh]

Something like:



----------------------------------
SELECT a.EmployeeID,
LAST(a.Salary),
MAX((a.Salary-b.[From])*b.Rate+b.Amt)

FROM myTable As a INNER JOIN TaxesRates As b
ON a.Salary <= b.[to]

GROUP BY a.EmployeeID
----------------------------------


The simple MAX() aggregate works on the assumption that higher is your
taxable revenue, higher is the tax to be paid.

LAST, in LAST(a.Salary), is used to aggregate the field. We could have also
use:


----------------------------------
SELECT a.EmployeeID,
a.Salary,
MAX((a.Salary-b.[From])*b.Rate+b.Amt)

FROM myTable As a INNER JOIN TaxesRates As b
ON a.Salary <= b.[to]

GROUP BY a.EmployeeID, a.Salary
----------------------------------

removing the LAST, but adding the field in the GROUP BY, but may be a little
bit slower.




Hoping it may help,
Vanderghast, Access MVP

 

[anyaley via AccessMonster.com]

Something like:

----------------------------------
SELECT a.EmployeeID,
LAST(a.Salary),
MAX((a.Salary-b.[From])*b.Rate+b.Amt)

FROM myTable As a INNER JOIN TaxesRates As b
ON a.Salary <= b.[to]

GROUP BY a.EmployeeID
----------------------------------

The simple MAX() aggregate works on the assumption that higher is your
taxable revenue, higher is the tax to be paid.

LAST, in LAST(a.Salary), is used to aggregate the field. We could have also
use:

----------------------------------
SELECT a.EmployeeID,
a.Salary,
MAX((a.Salary-b.[From])*b.Rate+b.Amt)

FROM myTable As a INNER JOIN TaxesRates As b
ON a.Salary <= b.[to]

GROUP BY a.EmployeeID, a.Salary
----------------------------------

removing the LAST, but adding the field in the GROUP BY, but may be a little
bit slower.

Hoping it may help,
Vanderghast, Access MVP

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

[quoted text clipped - 41 lines]

282,
140

Taxablepay or grosspay is a calculated field I am using in the access query.
It is Salary(Emptable) plus Allowances(Ern-Dedtable). Tax field is also a
calculated field in the query. Please simplify your solution.

 

[Michel Walsh]

In Jet, it does not matter if the expression in the SELECT clause is a
computed expression, or a field, since Jet allows expressions based on
expressions. Just replace "Salary" by whatever expression it should be.


Hoping it may help,
Vanderghast, Access MVP

Something like:

----------------------------------
SELECT a.EmployeeID,
LAST(a.Salary),
MAX((a.Salary-b.[From])*b.Rate+b.Amt)

FROM myTable As a INNER JOIN TaxesRates As b
ON a.Salary <= b.[to]

GROUP BY a.EmployeeID
----------------------------------

The simple MAX() aggregate works on the assumption that higher is your
taxable revenue, higher is the tax to be paid.

LAST, in LAST(a.Salary), is used to aggregate the field. We could have
also
use:

----------------------------------
SELECT a.EmployeeID,
a.Salary,
MAX((a.Salary-b.[From])*b.Rate+b.Amt)

FROM myTable As a INNER JOIN TaxesRates As b
ON a.Salary <= b.[to]

GROUP BY a.EmployeeID, a.Salary
----------------------------------

removing the LAST, but adding the field in the GROUP BY, but may be a
little
bit slower.

Hoping it may help,
Vanderghast, Access MVP

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

[quoted text clipped - 41 lines]

282,
140

Taxablepay or grosspay is a calculated field I am using in the access
query.
It is Salary(Emptable) plus Allowances(Ern-Dedtable). Tax field is also a
calculated field in the query. Please simplify your solution.