pick/calculate value from another table

[jozino01]

hi,
i am working on one report showing profit and commission for a specific project.
this looks quite easy; i just need to use value (%) for commission from another table, based on difference between listed price and actual price.
here is the table:

Net_Sales_Price Discount_fm_List_Price Comm

115 15 13.75
114 14 13.50
113 13 13.25
112 12 13.00
111 11 12.75
110 10 12.50
109 9 12.25
108 8 12.00
107 7 11.75
106 6 11.50
105 5 11.25
104 4 11.00
103 3 10.75
102 2 10.50
101 1 10.25
100 0 10.00
99 -1 9.70
98 -2 9.40
97 -3 9.10
96 -4 8.80
95 -5 8.50
94 -6 8.20
93 -7 7.90
92 -8 7.60
91 -9 7.30
90 -10 7.00
89 -11 6.70
88 -12 6.40
87 -13 6.10
86 -14 5.80
85 -15 5.50
84 -16 5.20
83 -17 4.90
82 -18 4.60
81 -19 4.30
80 -20 4.00
79 -21 3.70
78 -22 3.40
77 -23 3.10
76 -24 2.80
75 -25 2.50


so if dealer sells for original price, gets 10% commission and so on.
if sold for 75% of original price or less, gets 2.5%
if sold for 115% or more, gets 13.75%

original and actual prices are known.
what would be the best way to pick up/calculate correct percentage?

 

[lameid]

Typically I would store perecents as their decimal equivalents but it looks like you have them stored as integers. That makes the following option workable...

Add the above table to a query with your other values...

Then under Net_Sales_Price use the following criteria.

= IIF(INT(SoldPrice / OriginalPrice)> 115,115, IIF(INT
(SoldPrice / OriginalPrice)< 75, 75, INT(SoldPrice / OriginalPrice)))

Note that INT returns the integer value lower than the input unless an integer is passed to it.

 

[jozino01]

well, format can be changed (percentage or integer)
but how to get commission for sale price between 76% to 114% of original price (using query/calculation as data source for report field)?