How do I set access to figure differant pricing depending on cost. For
example I have a price list and if the part cost me $0.01-$2.00 I'd like to
times by 4 if it cost $2.01-$5.00 I'd like to times by 3.5 and so on, please
help, I am new at this and on a dead line.--
jimmy
Two suggestions:
- If it's just a few steps in the price ladder, and they are unlikely
to change in the future, use a Switch() function call:
[UnitCost] * Switch([UnitCost] < 2.00, 4, [UnitCost] < 5.00, 3.5,
[UnitCost] < 10.00, 3.0, ..., True, 1.00)
The arguments come in pairs; Access will go across the arguments, and
return the second member of the first pair for which the first member
is TRUE.
- If there are more than five or six price levels, or if (as is likely
to be the case!) you want to be able to easily maintain the values,
use a Query with a "non equi join". Create a table CostSteps with
values showing the cost ceiling and corresponding factor:
CostCeiling Factor
2.00 4.0
5.00 3.5
10.00 3.0
....
1000000000. 1.0
Join this table to your Query with no JOIN line but with a criterion
expression like
WHERE [CostCeiling] = (SELECT Min([CostCeiling] FROM CostSteps AS X
WHERE X.CostCeiling > [UnitCost])
John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps