Selections based off of tiered criteria

[New_2_Access]

I want to have access make a selection based off of certain criteria. Let's say that their are 3 sales people who sell 4 products. Based off of how many a salesperson sells they have different pay levels. If they sell one of prodcut A they are paid $1 each, if they sell more than 5 of product A, then they make $2 each. I want the database to calculate total commisions for each sales person for each product based upon this tiered stucture.

Any help would be appreciated.


D

[June7]

I haven't even looked a the db and already think this will require a VBA function just because the criteria seems complex and a single expression using nested IIf, Switch, Choose functions would be inadequate. Also, the condition requires an aggregate calculation to count the products.

How many products and how many commission tiers?

[recyan]

What is the role of the Thresholds table ?

Thanks

[New_2_Access]

I haven't even looked a the db and already think this will require a VBA function just because the criteria seems complex and a single expression using nested IIf, Switch, Choose functions would be inadequate. Also, the condition requires an aggregate calculation to count the products.

How many products and how many commission tiers?

I am somewhat familiar with VBA and was considering some sort of select case code, but I also wanted it to be adaptable to incorporate new products with different tiers if need be.

The example has 4 products with 3 tiers, but these numbers could change over time.

[New_2_Access]

The thresholds table is suppose to be once they reach a certain number of sales they move to a new tier of commissions. So if they sell 5 they are at tier 1 where they make $5 per sale, but if they sell 10 they are at tier 2 where they make 10$ per sale. Does that make sense?

More simplicity lets say that they make whatever the $10 per sale on all 10 rather than just the amount over the last tier.

[recyan]

Sorry, not the answers, but a few questions.
Taking the example of a single sale for Greg

Sales Data

Sales Order	Product	Sales Person	Quantity Sold
1	a	Greg	292

PayStructure

Product	Tier 1	Tier 2	Tier 3
a	5	8	10

Thresholds

Product	Tier 1	Tier 2	Tier 3
a	400	800	1200

1) What will be the calculation for Greg ?
2) What will be the calculation for Greg if we change Quantity Sold for Greg to 500 ?
3) What will be the calculation for Greg if we change Quantity Sold for Greg to 1000 ?
4) What will be the calculation for Greg if we change Quantity Sold for Greg to 1500 ?
5) Why does the Thresholds table have Product filed values as 1,2,3,4 ? Should it be a,b,c,d as in Paystructure table?
6) Why do we have 2 seperate tables for Paystructure andThresholds ?
7) Currently, have not given much thought, but was wondering whether we should have Paystructure table as something like

Product	TheTier	TheRate	TheThreshold
a	1	5	400
a	2	8	800
a	3	10	1200
b	1	2	1000
b	2	4	1200
b	3	6	1400
c	1
c	2
c	3
d	1
d	2
d	3

8) If we don't have a separate table for SalesPersons, we should be having that.

Thanks

[New_2_Access]

1) What will be the calculation for Greg ? Quantity Sold * Pay Rate (@tier level)
2) What will be the calculation for Greg if we change Quantity Sold for Greg to 500 ? 8*500=4,000
3) What will be the calculation for Greg if we change Quantity Sold for Greg to 1000 ? 8*1000=8000
4) What will be the calculation for Greg if we change Quantity Sold for Greg to 1500 ? 10*15000=15000
5) Why does the Thresholds table have Product filed values as 1,2,3,4 ? Should it be a,b,c,d as in Paystructure table? You are correct it should be
6) Why do we have 2 seperate tables for Paystructure andThresholds ? Pay structure was supposed to be commision per sale, thresholds is suppose to be sales required to make the jump to the higher commision

You're right I should have included a seperate table for sales people

Sorry, not the answers, but a few questions.
Taking the example of a single sale for Greg

Sales Data

Sales Order	Product	Sales Person	Quantity Sold
1	a	Greg	292

PayStructure

Product	Tier 1	Tier 2	Tier 3
a	5	8	10

Thresholds

Product	Tier 1	Tier 2	Tier 3
a	400	800	1200

1) What will be the calculation for Greg ?
2) What will be the calculation for Greg if we change Quantity Sold for Greg to 500 ?
3) What will be the calculation for Greg if we change Quantity Sold for Greg to 1000 ?
4) What will be the calculation for Greg if we change Quantity Sold for Greg to 1500 ?
5) Why does the Thresholds table have Product filed values as 1,2,3,4 ? Should it be a,b,c,d as in Paystructure table?
6) Why do we have 2 seperate tables for Paystructure andThresholds ?
7) Currently, have not given much thought, but was wondering whether we should have Paystructure table as something like

Product	TheTier	TheRate	TheThreshold
a	1	5	400
a	2	8	800
a	3	10	1200
b	1	2	1000
b	2	4	1200
b	3	6	1400
c	1
c	2
c	3
d	1
d	2
d	3

8) If we don't have a separate table for SalesPersons, we should be having that.

Thanks

[recyan]

1) What will be the calculation for Greg ? Quantity Sold * Pay Rate (@tier level)

My mistake. The question should have read
1) What will be the calculation for Greg , if the Quantity Sold is 292?

2) What will be the calculation for Greg if we change Quantity Sold for Greg to 500 ? 8*500=4,000
3) What will be the calculation for Greg if we change Quantity Sold for Greg to 1000 ? 8*1000=8000

Puzzled with use of 8 as Pay Rate for both.
In 2) 500 is below Tier2 Threshold 800
and
In 3) 1000 is above Tier2 Threshold 800.

6) Why do we have 2 seperate tables for Paystructure andThresholds ? Pay structure was supposed to be commision per sale, thresholds is suppose to be sales required to make the jump to the higher commision

Currently, we will loose focus on this & come back to it later

You're right I should have included a seperate table for sales people

Assume, you have done the same.

Thanks