Multiple Iff Statement

G

Guest

I have a query that has as one of its fields Total Sales, what I need to do
is add another field called Bonus, however the bonus differs depending on the
amt of the Total Sales. So if a salesman has sales of between $250,000 and
$350,000 his bonus is (Total Sales/1000)*.25, if his sales are between
$350,001 and $450,000 than his bonus is (Total Sales/1000)*.50, and so on.
I can get the formula to work for 1 but don't know how to get it to work on
multiple criteria. The formula I have so far is: Bonus: IIf([Total Sales]
Between 250000 And 350000,[ Total Sales]/10*0.25,[Total Sales]*0). It set it
to return a 0 value if it doesn't meet the criteria. How would I go about
getting it to go through the multiple criteria or should I set a field for
each criteria?
Any help would be greatly appreciated.
Thanks
 
G

Guest

Correction the should read: Bonus: IIf([Total Sales]
Between 250000 And 350000,[ Total Sales]/1000*0.25,[Total Sales]*0).

rascal said:
I have a query that has as one of its fields Total Sales, what I need to do
is add another field called Bonus, however the bonus differs depending on the
amt of the Total Sales. So if a salesman has sales of between $250,000 and
$350,000 his bonus is (Total Sales/1000)*.25, if his sales are between
$350,001 and $450,000 than his bonus is (Total Sales/1000)*.50, and so on.
I can get the formula to work for 1 but don't know how to get it to work on
multiple criteria. The formula I have so far is: Bonus: IIf([Total Sales]
Between 250000 And 350000,[ Total Sales]/10*0.25,[Total Sales]*0). It set it
to return a 0 value if it doesn't meet the criteria. How would I go about
getting it to go through the multiple criteria or should I set a field for
each criteria?
Any help would be greatly appreciated.
Thanks
 
G

Guest

It will make a difference if you have 4 or 30. You can nest IIF statements
by testing the first, give true, test second, give true, test xxxx ....

Or you can use a reference table (this example for mileage rate).
[RateTable-Mileage]
MinMiles MaxMiles Rate
0 500 0.2075
501 1500 0.1582
1501 999999 0.1521

SELECT Travel.Name, Travel.Mileage, [RateTable-Mileage].Rate
FROM Travel, [RateTable-Mileage]
WHERE (((Travel.Mileage) Between [MinMiles] And [MaxMiles]));

--
KARL DEWEY
Build a little - Test a little


rascal said:
Correction the should read: Bonus: IIf([Total Sales]
Between 250000 And 350000,[ Total Sales]/1000*0.25,[Total Sales]*0).

rascal said:
I have a query that has as one of its fields Total Sales, what I need to do
is add another field called Bonus, however the bonus differs depending on the
amt of the Total Sales. So if a salesman has sales of between $250,000 and
$350,000 his bonus is (Total Sales/1000)*.25, if his sales are between
$350,001 and $450,000 than his bonus is (Total Sales/1000)*.50, and so on.
I can get the formula to work for 1 but don't know how to get it to work on
multiple criteria. The formula I have so far is: Bonus: IIf([Total Sales]
Between 250000 And 350000,[ Total Sales]/10*0.25,[Total Sales]*0). It set it
to return a 0 value if it doesn't meet the criteria. How would I go about
getting it to go through the multiple criteria or should I set a field for
each criteria?
Any help would be greatly appreciated.
Thanks
 
J

John W. Vinson

I have a query that has as one of its fields Total Sales, what I need to do
is add another field called Bonus, however the bonus differs depending on the
amt of the Total Sales. So if a salesman has sales of between $250,000 and
$350,000 his bonus is (Total Sales/1000)*.25, if his sales are between
$350,001 and $450,000 than his bonus is (Total Sales/1000)*.50, and so on.
I can get the formula to work for 1 but don't know how to get it to work on
multiple criteria. The formula I have so far is: Bonus: IIf([Total Sales]
Between 250000 And 350000,[ Total Sales]/10*0.25,[Total Sales]*0). It set it
to return a 0 value if it doesn't meet the criteria. How would I go about
getting it to go through the multiple criteria or should I set a field for
each criteria?
Any help would be greatly appreciated.
Thanks

I'd use the Switch() function rather than IIF - or, perhaps better, build a
BonusRate table with three fields: SalesLow, SalesHigh and Rate.

The Switch() would be something like

Switch([Total Sales] <= 250000, 0, [Total Sales] <= 350000, .25, [Total Sales]
<= 450000, .5, <etc>) * [Total Sales] / 1000

Switch() takes arguments in pairs, and evaluates the pairs left to right; when
it first finds a pair for which the first argument is TRUE, it returns the
second member of the pair and quits.

The table-driven solution may be better because you can much more easily edit
the ranges and rates. You'ld have records like

0 250000 0.
250000 350000 0.25
350000 450000 0.50

and so on. You can join this to your Query with a JOIN clause like

ON [yourtable].[Total Sales] > [BonusRate].[SalesLow] AND [yourtable].[Total
Sales] <= [BonusRate].[SalesHigh]


John W. Vinson [MVP]
 
G

Guest

Thanks John, worked wonderfully.

John W. Vinson said:
I have a query that has as one of its fields Total Sales, what I need to do
is add another field called Bonus, however the bonus differs depending on the
amt of the Total Sales. So if a salesman has sales of between $250,000 and
$350,000 his bonus is (Total Sales/1000)*.25, if his sales are between
$350,001 and $450,000 than his bonus is (Total Sales/1000)*.50, and so on.
I can get the formula to work for 1 but don't know how to get it to work on
multiple criteria. The formula I have so far is: Bonus: IIf([Total Sales]
Between 250000 And 350000,[ Total Sales]/10*0.25,[Total Sales]*0). It set it
to return a 0 value if it doesn't meet the criteria. How would I go about
getting it to go through the multiple criteria or should I set a field for
each criteria?
Any help would be greatly appreciated.
Thanks

I'd use the Switch() function rather than IIF - or, perhaps better, build a
BonusRate table with three fields: SalesLow, SalesHigh and Rate.

The Switch() would be something like

Switch([Total Sales] <= 250000, 0, [Total Sales] <= 350000, .25, [Total Sales]
<= 450000, .5, <etc>) * [Total Sales] / 1000

Switch() takes arguments in pairs, and evaluates the pairs left to right; when
it first finds a pair for which the first argument is TRUE, it returns the
second member of the pair and quits.

The table-driven solution may be better because you can much more easily edit
the ranges and rates. You'ld have records like

0 250000 0.
250000 350000 0.25
350000 450000 0.50

and so on. You can join this to your Query with a JOIN clause like

ON [yourtable].[Total Sales] > [BonusRate].[SalesLow] AND [yourtable].[Total
Sales] <= [BonusRate].[SalesHigh]


John W. Vinson [MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top