Need help with an expression

[Mike_In_Ga]

I am writing a query to calculate commissions. The query is built except for the commission rate and commission calculation. The issue is that the commission rate is not stored anywhere in another table and the rule is dependent on more than one variable. The rule, in English, is as follows:

If the stockcode is not equal to "X" or "Y", then the commission rate is $.25 per quantity

If the Stockcode is equal to "X" or "Y", AND the Customer is "A", then the commission rate = $1 per quantity

If the Stockcode is equal to "X" or "Y", AND the Customer is NOT "A", then the commission rate = $.75 per quantity

I have the Stockcode, Quantity, and Customer fields pulled. I can calculate the commission amount once I get the rate. I am just having a hard time putting all the ifs, ands, or buts into the expression for the commission rate.

I would rather build an expression to figure out the rate rather than create a seperate table to house the stockcode, customer, and rate and simply pull it into the query. I know that sounds convoluted but in this instance it would be a far better solution.

Any advice? Thanks.

 

[jal]

SELECT IIF
(
    (StockCode = X or StockCode = Y) AND Customer = A
    , 1.00
    ,IIF 
    (
         (StockCode = X or StockCode = Y) AND NOT Customer = A
         ,.75
         ,.25
    )
)

 

[Mike_In_Ga]

Thanks. When the board went down I improvised and created two fields, a Y/N if the stockcode was one of the exception and a Y/N if the customer was one of the exceptions. I then created a third field that looked the flags of the other two using IIf to insert the amount. Same thing, I was just having issues with the syntax getting all into one statement. Thanks for the help and Ill use your code and blow away my two "crutch" fields.