Formula

[Srinivas]

Hi

Can any one help me to solve this

I have two fields amount and commission. Actual the scenario is like this
from 1 to 25000 - the bank will charge 25
as commision and from 25001 to 50000 - 50 then after 50001 to 55000 - 55 and
55001 to 60000 - 60 ..........etc i want formula up 100000. But if nothing
there like 0 it should display "0"

 

[John W. Vinson]

Hi

Can any one help me to solve this

I have two fields amount and commission. Actual the scenario is like this
from 1 to 25000 - the bank will charge 25
as commision and from 25001 to 50000 - 50 then after 50001 to 55000 - 55 and
55001 to 60000 - 60 ..........etc i want formula up 100000. But if nothing
there like 0 it should display "0"

Two suggestions:

Use the Switch() function as a calculated field in a Query. In a vacant Field
cell type

Commission: Switch([Amount] <= 25000, 25, [Amount] <= 50000, 50, [Amount] <=
55000, 55, <and so on>, True, 0)

Switch() takes pairs of arguments; evaluates them left to right; when it first
encounters a pair with the first member True it returns the second member of
that pair and quits.

OR... probably better... create a Commissions table with fields Low, High and
Commission and use a Query to look up the commission. This is preferable
because if the bank changes their policy you can just edit the table, rather
than digging into your code.

 

[KARL DEWEY]

Further with the Commissions table you can add a datetime field so as to
calculate what was when.
--
KARL DEWEY
Build a little - Test a little

Hi

Can any one help me to solve this

I have two fields amount and commission. Actual the scenario is like this
from 1 to 25000 - the bank will charge 25
as commision and from 25001 to 50000 - 50 then after 50001 to 55000 - 55 and
55001 to 60000 - 60 ..........etc i want formula up 100000. But if nothing
there like 0 it should display "0"

Two suggestions:

Use the Switch() function as a calculated field in a Query. In a vacant Field
cell type

Commission: Switch([Amount] <= 25000, 25, [Amount] <= 50000, 50, [Amount] <=
55000, 55, <and so on>, True, 0)

Switch() takes pairs of arguments; evaluates them left to right; when it first
encounters a pair with the first member True it returns the second member of
that pair and quits.

OR... probably better... create a Commissions table with fields Low, High and
Commission and use a Query to look up the commission. This is preferable
because if the bank changes their policy you can just edit the table, rather
than digging into your code.

 

[Srinivas]

I am not well versed with I tried it is working up to 80000 but more than
that switch function is giving an error "the expression you entered is too
complex". I want this commsion to be given up to 1000000. Can you please
tell me how to do the second option
--
srinivas

Further with the Commissions table you can add a datetime field so as to
calculate what was when.
--
KARL DEWEY
Build a little - Test a little

Hi

Can any one help me to solve this

I have two fields amount and commission. Actual the scenario is like this
from 1 to 25000 - the bank will charge 25
as commision and from 25001 to 50000 - 50 then after 50001 to 55000 - 55 and
55001 to 60000 - 60 ..........etc i want formula up 100000. But if nothing
there like 0 it should display "0"

Two suggestions:

Use the Switch() function as a calculated field in a Query. In a vacant Field
cell type

Commission: Switch([Amount] <= 25000, 25, [Amount] <= 50000, 50, [Amount] <=
55000, 55, <and so on>, True, 0)

Switch() takes pairs of arguments; evaluates them left to right; when it first
encounters a pair with the first member True it returns the second member of
that pair and quits.

OR... probably better... create a Commissions table with fields Low, High and
Commission and use a Query to look up the commission. This is preferable
because if the bank changes their policy you can just edit the table, rather
than digging into your code.

 

[John W. Vinson]

I am not well versed with I tried it is working up to 80000 but more than
that switch function is giving an error "the expression you entered is too
complex". I want this commsion to be given up to 1000000. Can you please
tell me how to do the second option

Create a table named Commissions with two fields: Floor and Commission. Floor
should be of the same datatype as the field you're using for the range -
currency I presume; Commission should be Currency if the value is a commission
amount, and perhaps Number... Float if it's a percentage.

Fill this table with all the desired amount values (just the low end of the
range); include a record with 0 as the Floor and the appropriate commission
for the lowest range.

When you need to calculate the commission in a Query use a Subquery by typing
into a vacant Field cell:

Commission: (SELECT TOP 1 Commission FROM Commissions WHERE Floor >= [amount]
ORDER BY Floor DESC

replacing [amount] with the appropriate fieldname from your table.