By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,489 Members | 1,954 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,489 IT Pros & Developers. It's quick & easy.

Conditional Formula for Computing Commissions

P: 18
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
Oct 14 '07 #1
Share this Question
Share on Google+
33 Replies


NeoPa
Expert Mod 15k+
P: 31,186
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.
Oct 14 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
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## %")
Oct 14 '07 #3

P: 18
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.
Oct 14 '07 #4

P: 18
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.
Oct 14 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Oct 14 '07 #6

P: 18
=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
Oct 14 '07 #7

P: 18
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?
Oct 14 '07 #8

P: 18
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?
Oct 14 '07 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Oct 14 '07 #10

P: 18
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?
Oct 14 '07 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Oct 14 '07 #12

P: 18
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!
Oct 14 '07 #13

NeoPa
Expert Mod 15k+
P: 31,186
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 :)
Oct 14 '07 #14

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Expand|Select|Wrap|Line Numbers
  1. =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## %")
Oct 14 '07 #15

P: 18
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.
Oct 14 '07 #16

NeoPa
Expert Mod 15k+
P: 31,186
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).
Oct 15 '07 #17

P: 18
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.
Expand|Select|Wrap|Line Numbers
  1. =Format(IIf(Sum([Post split])<500000, 0.03, 
  2. 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?
Oct 15 '07 #18

MMcCarthy
Expert Mod 10K+
P: 14,534
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?
Oct 15 '07 #19

MMcCarthy
Expert Mod 10K+
P: 14,534
I'm guessing here but would the following give you the actual amount ?


Expand|Select|Wrap|Line Numbers
  1. =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")
  2.  
Oct 15 '07 #20

P: 18
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.
Oct 15 '07 #21

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Oct 15 '07 #22

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


Expand|Select|Wrap|Line Numbers
  1. =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")
  2.  
This gave me a $ value. For the post split amount of $35,762.60 the commission is $1,072.88. Yeah, yeah, yeah!!!!!!!!!!!!!!!!
Oct 15 '07 #23

MMcCarthy
Expert Mod 10K+
P: 14,534
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?
Oct 15 '07 #24

P: 18
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!
Oct 15 '07 #25

P: 18
OK, the Commission is based on the Sum of Post Split. What is the payout based on exactly?
This is you finding my error.
Oct 15 '07 #26

MMcCarthy
Expert Mod 10K+
P: 14,534
This is you finding my error.
LOL!

It happens to us all. Let me know how you get on.
Oct 15 '07 #27

P: 18
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. :)
Oct 15 '07 #28

MMcCarthy
Expert Mod 10K+
P: 14,534
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. :)
Oct 15 '07 #29

NeoPa
Expert Mod 15k+
P: 31,186
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).
Oct 15 '07 #30

P: 18
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:
Expand|Select|Wrap|Line Numbers
  1. =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
Nov 25 '07 #31

NeoPa
Expert Mod 15k+
P: 31,186
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.
Expand|Select|Wrap|Line Numbers
  1. =Format(
  2. IIf(Sum([Post split]*[OS plan rate])<500000,
  3. Sum([Post split]*[OS plan rate]*[OS rate])*0.03,
  4. IIf(Sum([Post split]*[OS plan rate])<1000000,
  5. Sum([Post split]*[OS plan rate]*[OS rate])*0.0325,
  6. IIf(Sum([Post split]*[OS plan rate])<2000000,
  7. Sum([Post split]*[OS plan rate]*[OS rate])*0.035,
  8. IIf(Sum([Post split]*[OS plan rate])<3000000,
  9. Sum([Post split]*[OS plan rate]*[OS rate])*0.0375,
  10. Sum([Post split]*[OS plan rate]*[OS rate])*0.04)))),
  11. "$#,##0.00")
Nov 26 '07 #32

NeoPa
Expert Mod 15k+
P: 31,186
Just realised this can be tidied up further...
Expand|Select|Wrap|Line Numbers
  1. =Format(
  2. Sum([Post split]*[OS plan rate]*[OS rate])*
  3. IIf(Sum([Post split]*[OS plan rate])<500000,0.03,
  4. IIf(Sum([Post split]*[OS plan rate])<1000000,0.0325,
  5. IIf(Sum([Post split]*[OS plan rate])<2000000,0.035,
  6. IIf(Sum([Post split]*[OS plan rate])<3000000,0.0375,0.04)))),
  7. "$#,##0.00")
Hopefully this is also much easier to read and understand :)
Nov 26 '07 #33

NeoPa
Expert Mod 15k+
P: 31,186
...
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.
Nov 26 '07 #34

Post your reply

Sign in to post your reply or Sign up for a free account.