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 "complicated 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=Total 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## %")

[pollyanna]

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 :)

I want, and appreciate detailed information. I do require it right now. Thank you, thank you! I want to see the operation in motion and learn for the future. I don't mind providing info. I ceratinely don't mind direct requests. If I'm thick skulled, nudge me. I am an eager little newbie.

[NeoPa]

I want, and appreciate detailed information. I do require it right now. Thank you, thank you! I want to see the operation in motion and learn for the future. I don't mind providing info. I ceratinely don't mind direct requests. If I'm thick skulled, nudge me. I am an eager little newbie.

In that case, try Mary's solution below and report back how it works. If it doesn't, try to explain exactly what's wrong and we (Mary) will look at it further (J/K I will also keep an eye out and we will get you a solution assuming you provide the data we need).

[pollyanna]

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## %")

I made a new text box and called it "IS Commission" is has a % value in it. So, that gives me their % that their commission will be calculated with? and then I just make a new box to calculate that $ payout on that post split at that rate?

[MMcCarthy]

I made a new text box and called it "IS Commission" is has a % value in it. So, that gives me their % that their commission will be calculated with? and then I just make a new box to calculate that $ payout on that post split at that rate?

OK, the Commission is based on the Sum of Post Split. What is the payout based on exactly?

[MMcCarthy]

I'm guessing here but would the following give you the actual amount ?

=Format(IIf(Sum([Post split])<500000, Sum([Post split])*0.03, IIf(Sum([Post split])>=500000 and Sum([Post split])<1000000, Sum([Post split])*0.0325, IIf(Sum([Post split])>=1000000 and Sum([Post split])<2000000, Sum([Post split])*0.035, IIf(Sum([Post split])>=2000000 and Sum([Post split])<3000000, Sum([Post split])*0.075, IIf(Sum([Post split])>=3000000, Sum([Post split])*0.04))))), "$#,###,##0.00")
 

[pollyanna]

In that case, try Mary's solution below and report back how it works. If it doesn't, try to explain exactly what's wrong and we (Mary) will look at it further (J/K I will also keep an eye out and we will get you a solution assuming you provide the data we need).

Yeah! Thanks. I don't know if it would help you to have a screen shot of the report. Then you can see what I am saying is happening.

[MMcCarthy]

Yeah! Thanks. I don't know if it would help you to have a screen shot of the report. Then you can see what I am saying is happening.

Did you catch my latest post?

Also a screenshot probably wouldn't help but if you could create a test db which included your form and source data you could zip it and attach it to a post. You would just need to post first and then edit the post to attach the zip file.

[pollyanna]

I'm guessing here but would the following give you the actual amount ?

=Format(IIf(Sum([Post split])<500000, Sum([Post split])*0.03, IIf(Sum([Post split])>=500000 and Sum([Post split])<1000000, Sum([Post split])*0.0325, IIf(Sum([Post split])>=1000000 and Sum([Post split])<2000000, Sum([Post split])*0.035, IIf(Sum([Post split])>=2000000 and Sum([Post split])<3000000, Sum([Post split])*0.075, IIf(Sum([Post split])>=3000000, Sum([Post split])*0.04))))), "$#,###,##0.00")
 

This gave me a $ value. For the post split amount of $35,762.60 the commission is $1,072.88. Yeah, yeah, yeah!!!!!!!!!!!!!!!!

[MMcCarthy]

This gave me a $ value. For the post split amount of $35,762.60 the commission is $1,072.88.

Is this what you wanted?

[pollyanna]

Is this what you wanted?

Yes, but no. By reviewing the commission payout, I have realized my error.

The Outside sales is being paid:

OS Comm: [Post split]*[OS plan rate]*[OS rate]

In this request I have made for help with the IS Comm, I neglected to include the [IS plan rate] calculation. So, my $ value is coming out to high.

Whoops!

[pollyanna]

OK, the Commission is based on the Sum of Post Split. What is the payout based on exactly?

This is you finding my error.

[MMcCarthy]

This is you finding my error.

LOL!

It happens to us all. Let me know how you get on.

[pollyanna]

LOL!

It happens to us all. Let me know how you get on.

I corrected my error and it works perfect.Thank you! Mission accomplished. I am so excited and thankful for this example. Case closed. Until the next. :)

[MMcCarthy]

I corrected my error and it works perfect.Thank you! Mission accomplished. I am so excited and thankful for this example. Case closed. Until the next. :)

Happy to hear it. Another hurdle over and something new learned. :)

[NeoPa]

Pollyanna, You probably won't know this but the reason I posted so little on this was that it was night-time over here. Mary is not American - she's just a vampire :D Staying up until past 02:00 helping you with your problem (amongst other things).

[pollyanna]

You probably won't know this but the reason I posted so little on this was that it was night-time over here. Mary is not American - she's just a vampire :D Staying up until past 02:00 helping you with your problem (amongst other things).

1) Think comment as comming via Pony Express. I was left will the experience of being taken very good care of; beyond my expectation. I loved the colaboration of inputs from you both. Thank you for being thoughtful about contributing. It's very nice. I like things like that. And, I'll tell you, it is just a fabulous help to someone that is not were you are at, but wants to understand how to make their creation. 1,000 thank youS. :)

2) I have more questions about this formula ya'll helped me with (that Mary made). Here is the formula I settled on and am using:

=Format(IIf(Sum([Post split]*[OS plan rate])<500000,Sum([Post split]*[OS plan rate]*[OS rate])*0.03,IIf(Sum([Post split]*[OS plan rate])>=500000 And Sum([Post split]*[OS plan rate])<1000000,Sum([Post split]*[OS plan rate]*[OS rate])*0.0325,IIf(Sum([Post split]*[OS plan rate])>=1000000 And Sum([Post split]*[OS plan rate])<2000000,Sum([Post split]*[OS plan rate]*[OS rate])*0.035,IIf(Sum([Post split]*[OS plan rate])>=2000000 And Sum([Post split]*[OS plan rate])<3000000,Sum([Post split]*[OS plan rate]*[OS rate])*0.0375,IIf(Sum([Post split]*[OS plan rate])>=3000000,Sum([Post split]*[OS plan rate]*[OS rate])*0.04))))),"$#,##0.00")

Here are my questions: What if I want to sum this answer by month? What if I want to sum this answer by YTD? What do I add or do? I want to understand what to do. Will you help me more?

cheers!
pollyanna

[NeoPa]

Firstly Polyanna, your formula is more complicated than it need be.
Try the following instead. I have posted it with line-breaks for readability and ease of comprehension, but they should be removed when testing this in your database.
As the third parameter of a SQL IIf() call is, by definition, only processed if the condition is found to be False, you needn't check the lower bound of the range again.

=Format(
IIf(Sum([Post split]*[OS plan rate])<500000,
Sum([Post split]*[OS plan rate]*[OS rate])*0.03,
IIf(Sum([Post split]*[OS plan rate])<1000000,
Sum([Post split]*[OS plan rate]*[OS rate])*0.0325,
IIf(Sum([Post split]*[OS plan rate])<2000000,
Sum([Post split]*[OS plan rate]*[OS rate])*0.035,
IIf(Sum([Post split]*[OS plan rate])<3000000,
Sum([Post split]*[OS plan rate]*[OS rate])*0.0375,
Sum([Post split]*[OS plan rate]*[OS rate])*0.04)))),
"$#,##0.00")

[NeoPa]

Just realised this can be tidied up further...

=Format(
Sum([Post split]*[OS plan rate]*[OS rate])*
IIf(Sum([Post split]*[OS plan rate])<500000,0.03,
IIf(Sum([Post split]*[OS plan rate])<1000000,0.0325,
IIf(Sum([Post split]*[OS plan rate])<2000000,0.035,
IIf(Sum([Post split]*[OS plan rate])<3000000,0.0375,0.04)))),
"$#,##0.00")

Hopefully this is also much easier to read and understand :)

[NeoPa]

...
Here are my questions: What if I want to sum this answer by month? What if I want to sum this answer by YTD? What do I add or do? I want to understand what to do. Will you help me more?

cheers!
pollyanna

This is a hard one to answer as I don't know what your dataset contains.
In essence, you need to ensure that the dataset underlying the control is grouped by whichever data you want to group by. In a report this is controlled either by a Filter (if the control is in the Report Header) or by the Sorting and Grouping (if the control is in a sub-header). In a form, this would typically be controlled by a filter.