How to work out commission rates

[matpaulin]

Hi All

I am currently learning access in conjunction with my job data. I carry out interviews and get different rates for the amount of interviews conducted.
e.g. For interviews 1 to 10 I get AdFee1 for each interview and for interviews 11 -13 I get AdFee2 for 14 - 16 I get AdFee3 etc.

I have the AdFee1,2 etc rates in a table called FeeRates. I can query and get the total interviews for each Job but how do I calculate the total earned based on the rate for each "band". I could do it with excel but am struggling to achieve this with Access. I am presuming Nested IIF statements but am cautious due to division by zero errors. I haven't progressed to VBA as yet so any help would be great.

[June7]

Let x represent the total count of interviews, something like:

IIf(x<11,AdFee1*x,IIf(x<14,AdFee1*10+AdFee2*(x-10),AdFee1*10+AdFee2*3+AdFee3*(x-13)))