Results 1 to 11 of 11
  1. #1
    fountain27 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2019
    Posts
    25

    Calculate Commission

    I am trying to create a query to calculate a sales person commission (flat rate), but only after they reach a certain total. The commission is not effective until the threshold is reach and only applies to dollars over the threshold. I also want to show all the sold units with a column for commission. So the first rows would be empty until the threshold is reached.



    Example: Sales of $10,000 the rep receives no commission. Sales of $10,001 rep receives the commission only on the $1.

    I sincerely appreciate any suggestions.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not much info to be anything but very general. Maybe a calculated query field like
    Commission: Iff([Sales]>10000, [Sales]-10000*.1,Null)

    or research IIF expression syntax. I just picked 10% as a placeholder.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Also,
    Quote Originally Posted by fountain27 View Post
    I am trying to create a query to calculate a sales person commission (flat rate), but only after they reach a certain total.
    Is the threshold amount ($10,000 in this example) per sale or cumulative? If cumulative, over what time frame?

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make a tRate table
    rate1, 0, 1000
    rate2, 1001, 10000
    rate3, 10001, 50k....

    then in Person table, fill: PersonID ,rateVal

    then apply the amount due.

  5. #5
    fountain27 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2019
    Posts
    25
    Thank you for everyone's suggestions.

    The threshold is cumulative and over a calendar year. I created a quick table in Excel to illustrate.

    Item Sold Price Qty Total Cumulative Commission (10%)
    Item A $ 4,500.00 1 $ 4,500.00 $ -
    Item B $ 6,000.00 1 $ 6,000.00 $ 10,500.00 $ 50.00
    Item C $ 3,500.00 1 $ 3,500.00 $ 14,000.00 $ 1,400.00
    Item D $ 4,500.00 1 $ 4,500.00 $ 18,500.00 $ 1,850.00
    $ 18,500.00 $ 3,300.00

    After the sale of Item B the threshold has been met and $500 is eligible for the 10% commission. Since the threshold is met for the year the 10% commission is applied to the full Price for Item C and Item D and so on.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Is this for real, or is this some kind of homework problem? Each time you're calculating the commission to be paid, you're including sales amounts for which commission has already been paid. After 3 commission-qualifying sales, the commission rate is already at 42%: $3,300 paid on total qualifying sales of 14k. Surely the commission amounts should be

    Item Sold Price Qty Total Cumulative Commission (10%)
    Item A $4,500.00 1 $4,500.00 $ -
    Item B $6,000.00 1 $6,000.00 $10,500.00 $600.00
    Item C $3,500.00 1 $3,500.00 $14,000.00 $350.00
    Item D $4,500.00 1 $4,500.00 $18,500.00 $450.00
    $1,400.00

    Even if you say that the first sale becomes eligible after the threshold is met, the effective rate is still 12.5% on the first 4 sales, but it starts to rise thereafter. I'm no financial wiz but that scheme doesn't look normal. Maybe it's because of lack of java...
    I'll let one of the financial guys come up with what you need, assuming it makes sense to them.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    @micron - shouldn't commission for item be be $50? and the total $850?

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by Ajax View Post
    @micron - shouldn't commission for item be be $50? and the total $850?
    Probably, but then who knows the rules exactly? I based it on the sale of 6000 because that sale put you over 10K. Isn't that how it normally works? I'm just trying to point out that it doesn't make sense to me to pay commission on sales for which you've already paid commission. That's what you're doing by including prior sales in the subsequent commission calculations, no?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    fountain27 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2019
    Posts
    25

    Unhappy

    Quote Originally Posted by Micron View Post
    Is this for real, or is this some kind of homework problem? Each time you're calculating the commission to be paid, you're including sales amounts for which commission has already been paid. After 3 commission-qualifying sales, the commission rate is already at 42%: $3,300 paid on total qualifying sales of 14k. Surely the commission amounts should be

    Item Sold Price Qty Total Cumulative Commission (10%)
    Item A $4,500.00 1 $4,500.00 $ -
    Item B $6,000.00 1 $6,000.00 $10,500.00 $600.00
    Item C $3,500.00 1 $3,500.00 $14,000.00 $350.00
    Item D $4,500.00 1 $4,500.00 $18,500.00 $450.00
    $1,400.00

    Even if you say that the first sale becomes eligible after the threshold is met, the effective rate is still 12.5% on the first 4 sales, but it starts to rise thereafter. I'm no financial wiz but that scheme doesn't look normal. Maybe it's because of lack of java...
    I'll let one of the financial guys come up with what you need, assuming it makes sense to them.
    Yes you are 100% correct. I make the table in a hurry and forgot to take out the prior sales.

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    OK, then I'd suggest re-posting your expected results so that the "rules" can be precisely understood. Or is it just 10% of Total (which is price*qty) when Cumulative > 10k? Regardless, you are missing the sales person ID here. I don't see a way of basing this on a particular person with what you've revealed. If all you want is an expression and you've taken care of the sales id filter somehow, then try

    Commission: Iff([Cumulative]>10000, [Total]*.1,Null)

    but I think I'd rather do this in a form where a control showing (10,000 - DSum of personID's [Total]) and another control that multiplies that by the rate if it exceeds the threshold because your little table suggests you're storing cumulative and other calculations in a table, which is ill advised. Besides, if you had a rates table (to allow changes to rates if that would be across the board) or even different rates for different sales persons, then the form would be much more flexible.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    fountain27 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2019
    Posts
    25
    You are correct. It's just 10% after sales reach $10,001. In the Access version I do have an employee ID in tbl_Employee and Price in tbl_Price. The totals are coming in query to select just one sales rep use a calculated number of Price*Quantity.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 11-09-2017, 12:45 AM
  2. working out the commission
    By "OJ" in forum Queries
    Replies: 2
    Last Post: 02-07-2016, 06:02 PM
  3. simple agent/commission report
    By smil27 in forum Access
    Replies: 46
    Last Post: 07-06-2013, 02:06 PM
  4. How to work out commission rates
    By matpaulin in forum Queries
    Replies: 1
    Last Post: 02-07-2013, 06:15 PM
  5. How to calculate?
    By cap.zadi in forum Queries
    Replies: 1
    Last Post: 11-09-2011, 07:29 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums