Returning results based on a total amount

[Guest]

Hi

I have developed a payslip that includes a total amount. I want to have the
report automatically calculate the tax anount payable, however, different tax
rates apply depending on the total amount.

Eg. 113 to 119 = $1
120 to 130 = $2
etc etc.

I have created a table called TaxRates and have created 3 columns
[amount from] [amount to] [TaxRate]

this way i can update the rates without touching a query

how do i use the table to return a result to the report based on the
calculated total in the report or query?

thanks

Ben

 

[George Nicholson]

as an SQL string: "SELECT [TaxAmount] FROM TaxTable WHERE ([PayTotal] >
[AmountFrom]) AND ([PayTotal] <= [AmountTo])"

or

DLookup("[TaxAmount]","TaxTable", "([PayTotal] > [AmountFrom]) AND
([PayTotal] <= [AmountTo])")

Replace [PayTotal] with a variable or parameter for the amount you want to
"look up" and adjust the string accordingly. For example:

"(" & myVariable & " > [AmountFrom]) AND (" & myVariable & " <=
[AmountTo])"

 

[John Vinson]

Hi

I have developed a payslip that includes a total amount. I want to have the
report automatically calculate the tax anount payable, however, different tax
rates apply depending on the total amount.

Eg. 113 to 119 = $1
120 to 130 = $2
etc etc.

I have created a table called TaxRates and have created 3 columns
[amount from] [amount to] [TaxRate]

this way i can update the rates without touching a query

how do i use the table to return a result to the report based on the
calculated total in the report or query?

You can use a "Non Equi Join" query to do this. Create a Query (to
start with) joining [Amount] to [Amount To]. Now go into SQL view of
the query and edit

ON [yourquery].[Amount] = [Taxrange].[Amount To]

to

ON [yourquery].[Amount] >= [Taxrange].[Amount From] AND
[yourquery].[Amount] <= [Taxrange].[Amount To]

Note that you must be very careful with this table structure: as you
have it set up there could be gaps or overlaps in the ranges. You
might want to consider having a threshold table instead, using Max()
to find the highest threshold less than the amount.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Thanks John and George

I am still having trouble. i have tried putting the SQL string in the
control box on the report and also tried it in the query itself. I am not
sure where to put the code.

In my query i have two tables - TMDetails and TMStats

TMDetails is for entering details of a Telemarketer (Name, hourly Rate etc)
TMStats is for entering start and finish times etc.

in the query i have a column called [totalamount], the total amount is a sum
of [totalhours]*[hourlyrate] (totalhours is worked out with the datediff
function)

I also have set up a table called TaxTable. In this table i have 3 columns
[payfrom] [payto] [taxamount]

I want another column in my query called [taxamount]. this column needs to
return a value based on the [totalamount] as the tax amount will depend on
the total amount.

I understand the SQL string you gave me is to look at the TaxTable and
return a value based on the [payfrom] and [payto] amounts. However, when i
try and put it in my query it askes me to enter parameter values for
totalamount..

am i doing something wrong? can you give me more specific instructions on
how i make this work.

Thanks

Ben

Hi

I have developed a payslip that includes a total amount. I want to have the
report automatically calculate the tax anount payable, however, different tax
rates apply depending on the total amount.

Eg. 113 to 119 = $1
120 to 130 = $2
etc etc.

I have created a table called TaxRates and have created 3 columns
[amount from] [amount to] [TaxRate]

this way i can update the rates without touching a query

how do i use the table to return a result to the report based on the
calculated total in the report or query?

You can use a "Non Equi Join" query to do this. Create a Query (to
start with) joining [Amount] to [Amount To]. Now go into SQL view of
the query and edit

ON [yourquery].[Amount] = [Taxrange].[Amount To]

to

ON [yourquery].[Amount] >= [Taxrange].[Amount From] AND
[yourquery].[Amount] <= [Taxrange].[Amount To]

Note that you must be very careful with this table structure: as you
have it set up there could be gaps or overlaps in the ranges. You
might want to consider having a threshold table instead, using Max()
to find the highest threshold less than the amount.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

I understand the SQL string you gave me is to look at the TaxTable and
return a value based on the [payfrom] and [payto] amounts. However, when i
try and put it in my query it askes me to enter parameter values for
totalamount..

You can't reuse a calculated field in a further calculation. I'd
suggest either basing a second query on your current query, or using
[totalhours]*[hourlyrate] instead of [totalamount] in the Join.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[George Nicholson]

As i've written you off-group, you should use 2 queries to do this. First,
do a Totals Query to get an accurate TotalAmount, then base a 2nd query off
of that to get your tax calculation.

Substituting (TotalHours * TotalPayRate) for TotalAmount in a TaxTable
lookup is NOT recommended when you really want to
DLookup(....Sum([TotalAmount])...), and you can't do that.
Sum(Dlookup(...)), even if allowed by the SQL compiler, is sure to give you
inaccurate results eventually (at least it would with US tax schedules).

It's a 2 step process. No way around it.

--
George Nicholson

Remove 'Junk' from return address.

Thanks John and George

I am still having trouble. i have tried putting the SQL string in the
control box on the report and also tried it in the query itself. I am not
sure where to put the code.

In my query i have two tables - TMDetails and TMStats

TMDetails is for entering details of a Telemarketer (Name, hourly Rate
etc)
TMStats is for entering start and finish times etc.

in the query i have a column called [totalamount], the total amount is a
sum
of [totalhours]*[hourlyrate] (totalhours is worked out with the datediff
function)

I also have set up a table called TaxTable. In this table i have 3 columns
[payfrom] [payto] [taxamount]

I want another column in my query called [taxamount]. this column needs to
return a value based on the [totalamount] as the tax amount will depend on
the total amount.

I understand the SQL string you gave me is to look at the TaxTable and
return a value based on the [payfrom] and [payto] amounts. However, when i
try and put it in my query it askes me to enter parameter values for
totalamount..

am i doing something wrong? can you give me more specific instructions on
how i make this work.

Thanks

Ben

Hi

I have developed a payslip that includes a total amount. I want to have
the
report automatically calculate the tax anount payable, however,
different tax
rates apply depending on the total amount.

Eg. 113 to 119 = $1
120 to 130 = $2
etc etc.

I have created a table called TaxRates and have created 3 columns
[amount from] [amount to] [TaxRate]

this way i can update the rates without touching a query

how do i use the table to return a result to the report based on the
calculated total in the report or query?

You can use a "Non Equi Join" query to do this. Create a Query (to
start with) joining [Amount] to [Amount To]. Now go into SQL view of
the query and edit

ON [yourquery].[Amount] = [Taxrange].[Amount To]

to

ON [yourquery].[Amount] >= [Taxrange].[Amount From] AND
[yourquery].[Amount] <= [Taxrange].[Amount To]

Note that you must be very careful with this table structure: as you
have it set up there could be gaps or overlaps in the ranges. You
might want to consider having a threshold table instead, using Max()
to find the highest threshold less than the amount.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps