Switch? Needed value based upon where a # falls within a range of

[Guest]

I have a query (query A) that determines an actual sales percentage per
worker. That percentage determines how much of a bonus that individual will
receive.
For example:

Sales % Eligible Bonus

=90% = 100%
=80% = 90%
=70% = 75%
=69% = 60%
=68% = 59%

(etc.. KEEPS GOING DOWN to 0% in Sales % and Eligible Bonus %)

I’ve used ‘Switch’ functions in queries before, but never with this many
elements. I tried using ‘Switch’ and could not get it to work.

Because the ‘Sales %’ and ‘Eligible Bonus’ percentages never change, is
there a way to put those in a table to use to compare to (to retrieve the
‘Eligible Bonus’ amount). For example, if my query (query A) returns that
someone’s ‘actual sales percentage’ is 88.6, can the 88.6 be compared to the
‘Sales %’ and return the appropriate ‘Eligible Bonus’ of 90%? If so, what is
the best way to do this (given my avg query skill level)?

Any assistance that you can provide would be greatly appreciated.

 

[Marshall Barton]

I have a query (query A) that determines an actual sales percentage per
worker. That percentage determines how much of a bonus that individual will
receive.
For example:

Sales % Eligible Bonus
(etc.. KEEPS GOING DOWN to 0% in Sales % and Eligible Bonus %)

I’ve used ‘Switch’ functions in queries before, but never with this many
elements. I tried using ‘Switch’ and could not get it to work.

Because the ‘Sales %’ and ‘Eligible Bonus’ percentages never change, is
there a way to put those in a table to use to compare to (to retrieve the
‘Eligible Bonus’ amount). For example, if my query (query A) returns that
someone’s ‘actual sales percentage’ is 88.6, can the 88.6 be compared to the
‘Sales %’ and return the appropriate ‘Eligible Bonus’ of 90%? If so, what is
the best way to do this (given my avg query skill level)?

Absolutely use a table for this.

The most straightforward way is to have three fields in the
bonus table:
Low Double
High Double
Bonus Double

Then use a non-equi-join to retrieve the bonus percent for
each worker. The SQL would look something like:

SELECT A.*, B.Bonus
FROM A INNER JOIN Bonuses As B
ON A.sales >= B.Low And A.sales <= B.Hhigh

You will not be able to create that kind of Join in the
query design grid so switch the query to SQL view and fix it
up there.

 

[John Vinson]

I have a query (query A) that determines an actual sales percentage per
worker. That percentage determines how much of a bonus that individual will
receive.
For example:

Sales % Eligible Bonus
(etc.. KEEPS GOING DOWN to 0% in Sales % and Eligible Bonus %)

I’ve used ‘Switch’ functions in queries before, but never with this many
elements. I tried using ‘Switch’ and could not get it to work.

There's no real limit to Switch() as a function, to my knowledge; the
only limit you're likely to run into is that an expression in a Query
has a size limit (2048 bytes IIRC). What "doesn't work"?

Because the ‘Sales %’ and ‘Eligible Bonus’ percentages never change, is
there a way to put those in a table to use to compare to (to retrieve the
‘Eligible Bonus’ amount). For example, if my query (query A) returns that
someone’s ‘actual sales percentage’ is 88.6, can the 88.6 be compared to the
‘Sales %’ and return the appropriate ‘Eligible Bonus’ of 90%? If so, what is
the best way to do this (given my avg query skill level)?

Any assistance that you can provide would be greatly appreciated.

A "Non Equi Join" query is the ticket here. Create a table tblBonus
with three fields: SalesLow, SalesHigh, Bonus, with values like

0.9; 1.01; 1.0
0.8; 0.9; 0.9
0.7; 0.8; 0.75

Then create a new Query joining your sales percentage field - first -
to SalesLow.

Open the query in SQL view and edit the join clause from

ON yourtable.salesfield = tblBonus.SalesLow

to

ON yourtable.salesfield >= tblBonus.SalesLow
AND yourtable.salesfield < tblBonus.SalesHigh


John W. Vinson[MVP]

 

[Guest]

Very Helpful. Thanks!!!!

There's no real limit to Switch() as a function, to my knowledge; the
only limit you're likely to run into is that an expression in a Query
has a size limit (2048 bytes IIRC). What "doesn't work"?


A "Non Equi Join" query is the ticket here. Create a table tblBonus
with three fields: SalesLow, SalesHigh, Bonus, with values like

0.9; 1.01; 1.0
0.8; 0.9; 0.9
0.7; 0.8; 0.75

Then create a new Query joining your sales percentage field - first -
to SalesLow.

Open the query in SQL view and edit the join clause from

ON yourtable.salesfield = tblBonus.SalesLow

to

ON yourtable.salesfield >= tblBonus.SalesLow
AND yourtable.salesfield < tblBonus.SalesHigh


John W. Vinson[MVP]