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

[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.