Hello

I am trying to create a database to calculate commissions on a sale
based on a tiered commission schedule and am having trouble with how
to design the tables and relationships to store the info needed.

Each sale will have 1 or 2 sales reps which are assigned to a tier.
For example:
repA is assigned to Tier 1
repB is assigned to Tier 2
repC is assigned to Tier 3

If repB & repC completes a sale then the following commissions are
paid out
repA receives 4% of sale amount
repB receives 8% of sale amount
repC receives 4% of sale amount

If repA completes a sale then:
repA receives 10% of sale amount

What fields should i include in the table to help determine the
commissions paid out. We may add more Tiers in the future so I would
like to design the structure to be easliy scalable.

Thank you in advance for your time.
Matt

On Oct 10, 11:22*am, spima05 <mspilot... (AT) gmail (DOT) com> wrote:
tblRep
repCode
repName

tblOrder
salesOrder


tblOrderRep
salesOrder
repCode
tier
commissionRate

I save the commissionRate in case the business rules change
tomorrow

and then you have a form to calculate the commission, and it applies
the 4, 8, 4, 10% rules

On Oct 10, 6:51*pm, Roger <lesperan... (AT) natpro (DOT) com> wrote:
Thank you for your response Roger. I am a little confused with the
tblOrderRep, should i include a field for the 2nd rep that might be
part of the sale?

Thank you in advance.

And I would appreciate suggestions from anyone else who may ideas.

Thank you.
Matt

On Oct 10, 7:50*pm, spima05 <mspilot... (AT) gmail (DOT) com> wrote:
no, not another field, you just need a second record in tblOrderRep
that way, it's scalable to support as many reps per order, as required

I missed that 'tier' is attached to a salesrep, so here's a new design
with sample data

tblRep
repCode
repName
tier

1 Joe 1
2 Mary 1
3 Jack 2
4 John 3

tblOrder
salesOrder

123
124
125
126
127

tblOrderRep
salesOrder
repCode
tier
commissionRate

123 1 1 4%
124 1 1 4%
124 3 2 8%
125 2 1 4%
125 3 2 8%
125 4 3 4%

On Oct 11, 9:56*am, Roger <lesperan... (AT) natpro (DOT) com> wrote:
Hello Roger

Thank you again for the information you provided. Since some of the
fields are stored in multiple tables does the user have to enter the
values multiple times for each table? For example, tblOrder and
tblOrderRep have the salesOrder field. Does the user have to enter the
values for salesOrder in both tables or does one of the tables
automatically get populated when the values are entered into the
other.

Thank you for your help
Matt

On Oct 11, 11:16*pm, spima05 <mspilot... (AT) gmail (DOT) com> wrote:
you never want to update tables directly
you would create a continuous form for tblOrderRep
then you would create a single form for tblOrder, to which you would
add frmOrderRep as a 'subform'

the link between form frmOrder and subform frmOrderRep would be
'salesOrder', so access would automatically set the value of
salesOrder in frmOrderRep to what is the current value of salesOrder
in frmOrder

On Oct 12, 10:08*am, Roger <lesperan... (AT) natpro (DOT) com> wrote:
Thank you Roger, the information you provided has helped tremendously!
I have another question, if i want to add a flag to show which rep was
the person that initiated the sale should i add this field to the
tblOrderRep and can it be a Yes/No field? I read an article that said
to stay away from Yes/No flags in databases.

Thank you again for your time and valuable advice.
Matt