Conditional Formula for Computing Commissions

[pollyanna]

Hi there,

I hope this is simple for someone. I always figure things out myself, but I thought it would be real nice to see if anyone else out there thought this was super simple and knew the answer. Here goes:

I am working on an Access report that details individual sales by month, then sums the total at the bottom (I have all this part done), and based on the sum, uses the appropriate percentage rate to calculate the commission. I hope I am making sense.

Here is the pay rate based on total sales.

0 – 499K = .03%
500k – 999K = .0325%
1M – 1.9M = .035%
2M – 2.9M = .0375%
3M+ = .04%

How do I write a query formula that looks at the total sales and based on the total sales number uses the correct rate to calculate the commission?

Hope this sounds fun for someone. Probably old as the hills.

Thank you and regards,
pollyanna

[NeoPa]

You have two options it seems to me :

1. Do a complicated IIf() structure (with only 5 levels it wouldn't be THAT complicated).
2. Create a table with the structure in it and link to that table.

Do you need more details on how to do this? Is this simply an intellectual exercise?
If you want a fleshing out of either idea please respond with what you need.

[MMcCarthy]

Something like ...

=Format(IIf(Sum ([SalesField])<500000, 0.03, IIf(Sum([SalesField])>=500000 and Sum(([SalesField])<1000000, 0.0325, IIf(Sum([SalesField])>=1000000 and Sum(([SalesField])<2000000, 0.035, IIf(Sum([SalesField])>=2000000 and Sum(([SalesField])<3000000, 0.075, IIf(Sum([SalesField])>=3000000, 0.04)))))/100, "##0.0## %")

[pollyanna]

You have two options it seems to me :

1. Do a complicated IIf() structure (with only 5 levels it wouldn't be THAT complicated).
2. Create a table with the structure in it and link to that table.

Do you need more details on how to do this? Is this simply an intellectual exercise?
If you want a fleshing out of either idea please respond with what you need.

Greetings! Thank you.

I am inclined to pick the "complicate d IIf() structure (with only 5 levels it wouldn't be THAT complicated) option."

This is what I was tryin got do own my own, but I didn't know how to structure the levels.

Everything is an intellectual exercise. :) It is also a little road block in the sales report I am making.

Yes, please. I would like help.

[pollyanna]

Something like ...

=Format(IIf(Sum ([SalesField])<500000, 0.03, IIf(Sum([SalesField])>=500000 and Sum(([SalesField])<1000000, 0.0325, IIf(Sum([SalesField])>=1000000 and Sum(([SalesField])<2000000, 0.035, IIf(Sum([SalesField])>=2000000 and Sum(([SalesField])<3000000, 0.075, IIf(Sum([SalesField])>=3000000, 0.04)))))/100, "##0.0## %")

Thank you very much! This looks like what flashed through my mind, but I have no yet down coded the details.

In Access, were is the best place to put such a formula? I currentley intend to create it in a field in the report based query, and then just add it to the report design layout.

[MMcCarthy]

Thank you very much! This looks like what flashed through my mind, but I have no yet down coded the details.

In Access, were is the best place to put such a formula? I currentley intend to create it in a field in the report based query, and then just add it to the report design layout.

I wouldn't do that, just calculate it dynamically on the report. This is designed to work in the control source of the textbox.

[pollyanna]

=Format(IIf(Sum ([Billing amnt])<500000, 0.03, IIf(Sum([Billing amnt])>=500000 and Sum(([Billing amnt])<1000000, 0.0325, IIf(Sum([Billing amnt])>=1000000 and Sum(([Billing amnt])<2000000, 0.035, IIf(Sum([Billing amnt])>=2000000 and Sum(([Billing amnt])<3000000, 0.075, IIf(Sum([Billing amnt])>=3000000, 0.04)))))/100, "##0.0## %")

Does there not need to be a calculation between the sum variables and and the relavant percentage variables? Something like

=Format([if(Sum([Billing amnt])<500000, then sum=[Billing amnt]*0.03

[pollyanna]

I wouldn't do that, just calculate it dynamically on the report. This is designed to work in the control source of the textbox.

Ok. How do I do that?

[pollyanna]

Ok. How do I do that?

I can see where to put the formula. When I right click and go to preference for the text box, I select the data tab and make my entry in the control souce box. That is where I put this formula I am wanting. Right?

[MMcCarthy]

I can see where to put the formula. When I right click and go to preference for the text box, I select the data tab and make my entry in the control souce box. That is where I put this formula I am wanting. Right?

Thats right Pollyanna.

[pollyanna]

Thats right Pollyanna.

Ok. Good. Thank you. That is re-affirming to know I am in the ball park. It must be funny for you as a Master to see fresh greens. Waa-waa. LOL!

I am getting an error when I use what you gave me. I know it was just a sample to build on. I am using as a base for understanding how to do what I am trying to do. I am of course switching out the field name. I did find more if/then on this site. I am now trying to know how and why to"calculate it dynamically" and getting the the if/then correct, or the most efficient way correct.

I have a box built on the sales persons summary line. I can just build it with the billing summary number next to it, right?

[MMcCarthy]

Ok. Good. Thank you. That is re-affirming to know I am in the ball park. It must be funny for you as a Master to see fresh greens. Waa-waa. LOL!

I am getting an error when I use what you gave me. I know it was just a sample to build on. I am using as a base for understanding how to do what I am trying to do. I am of course switching out the field name. I did find more if/then on this site. I am now trying to know how and why to"calculate it dynamically" and getting the the if/then correct, or the most efficient way correct.

I have a box built on the sales persons summary line. I can just build it with the billing summary number next to it, right?

OK, what is the name of your textbox which gives total sales and where is it placed. Also what is the code in the Control Source of that textbox.

[pollyanna]

OK, what is the name of your textbox which gives total sales and where is it placed. Also what is the code in the Control Source of that textbox.

Textboxname=Tot al Post Split $$$
Control Source=Sum([Post split])

The text box name is above the control source box and both are placed in the "IS FullName Footer" one above the other.

Thank you!

[NeoPa]

With the data Mary's asked for we're in a better position to answer your question at a more detailed level (which you seem to require). It's not a problem, but only possible if the info is provided :)

[MMcCarthy]

In the footer you need a new textbox (to reflect the % pay rate) and put the following code in the Control Source property of that textbox.

=Format(IIf(Sum([Post split])<500000, 0.03, IIf(Sum([Post split])>=500000 and Sum([Post split])<1000000, 0.0325, IIf(Sum([Post split])>=1000000 and Sum([Post split])<2000000, 0.035, IIf(Sum([Post split])>=2000000 and Sum([Post split])<3000000, 0.075, IIf(Sum([Post split])>=3000000, 0.04)))))/100, "##0.0## %")