Table Design Question

[Joe Williams]

I have to develop an access app that records commissions for selling
maintenance contracts. I started with a simple sales table but quickly ran
into trouble because sometimes the commissions are paid in full at the time
of the sale, and some times they are paid over time as long as the
maintenance contract is in effect. (Example, one sale might be a flat
commission of $10 for that sale, another commission might get paid $5.00 per
month as long as the contract is in effect)

Is it proper database design to have a table that creates a separate record
for each month as long as the maintenance contract is in effect? If so, how
would I got about doing that? Or do I stick with a table that has a single
record for the sale and use some type of report logic to display the
recurring commission each month?

How is this type of situation normally handled? Thanks in advance

joe

 

[Allen Browne]

Hi Joe

You will need to be quite comfortable with VBA and the SQL view of queries
to achieve this.

For any Contract, you want fields to record the details of the contract, and
also these fields:
ContractID primary key
ContractDate Date/Time
ClientID foreign key to Client.ClientID
SoldByID foreign key to Employee.EmployeeID
InitalCommission Currency
RecurCommission Currency
RecurPeriodType Text (4) contains "d", "m", "q", or "yyyy"
RecurFrequency Number (Long) number of days/months between
commission pay'ts.
InactiveDate Date/Time date the contract ceases (blank for
current ones).
If the commision on a contract may need to be shared between multiple
employees, this all needs to be in a related table. If the commissions are
tiered scales, that's a whole other story.

For the commission payments, you want a Payment table:
PaymentID primary key
PaymentDate Date/time
EmployeeID foreign key to Employee.EmployeeID. Who was paid.
and a PaymentDetail table:
PaymentID foreign key to Payment.PaymentID
ContractID foreign key to Contract.ContractID
PaymentWasDue Date/Time. The date this payment was owed to the
employee.
Amount Currency

You can now calculate when a commission payment is next due, as:
DateAdd([RecurPeriodType], [RecurFrequency],
Nz([MaxOfPaymentDate], [ContractDate]))
where MaxOfPaymentDate is calculated as:
SELECT Max([PaymentDate]) FROM CommissionDetail
WHERE CommissionDetail.ContractID = xxx)

Now you know how to calculate the next payment date, you can program a
routine to select all the contracts that have a next due date up to the end
of the period (month)?, and append the records to your Payment and
PaymentDetail tables, using recordsets and INSERT query statements. This
provides a full history of what payments were made on what dates to which
sales representatives, and for which contracts. Additionally, it allows the
flexibility to add bonus payments or skip payments if your company hits
unusual circumstances, since the calcuation is always done based on one
period after the last PaymentWasDue date.

Leave the PaymentWasDue field blank for the payments of the
InitialCommssion: it relates only to recurring commissions.

This kind of approach, where you provide the full trail of what was paid to
whom when and why, is the only way to not only get the database working
correctly, but be able to demonstrate that it is working correctly, and to
fix it and correct any over/under payments if you did get it wrong.

HTH

 

[Joe Williams]

Allen

Wow. Thank you for the great response. I am following you on most of what
you are suggesting. Can you elaborate on how to "append the records to your
Payment and
PaymentDetail tables, using recordsets and INSERT query statements." or
provide a link that shows how to do this?

Thanks again.

- Joe

Hi Joe

You will need to be quite comfortable with VBA and the SQL view of queries
to achieve this.

For any Contract, you want fields to record the details of the contract,
and also these fields:
ContractID primary key
ContractDate Date/Time
ClientID foreign key to Client.ClientID
SoldByID foreign key to Employee.EmployeeID
InitalCommission Currency
RecurCommission Currency
RecurPeriodType Text (4) contains "d", "m", "q", or "yyyy"
RecurFrequency Number (Long) number of days/months between
commission pay'ts.
InactiveDate Date/Time date the contract ceases (blank for
current ones).
If the commision on a contract may need to be shared between multiple
employees, this all needs to be in a related table. If the commissions are
tiered scales, that's a whole other story.

For the commission payments, you want a Payment table:
PaymentID primary key
PaymentDate Date/time
EmployeeID foreign key to Employee.EmployeeID. Who was paid.
and a PaymentDetail table:
PaymentID foreign key to Payment.PaymentID
ContractID foreign key to Contract.ContractID
PaymentWasDue Date/Time. The date this payment was owed to the
employee.
Amount Currency

You can now calculate when a commission payment is next due, as:
DateAdd([RecurPeriodType], [RecurFrequency],
Nz([MaxOfPaymentDate], [ContractDate]))
where MaxOfPaymentDate is calculated as:
SELECT Max([PaymentDate]) FROM CommissionDetail
WHERE CommissionDetail.ContractID = xxx)

Now you know how to calculate the next payment date, you can program a
routine to select all the contracts that have a next due date up to the
end of the period (month)?, and append the records to your Payment and
PaymentDetail tables, using recordsets and INSERT query statements. This
provides a full history of what payments were made on what dates to which
sales representatives, and for which contracts. Additionally, it allows
the flexibility to add bonus payments or skip payments if your company
hits unusual circumstances, since the calcuation is always done based on
one period after the last PaymentWasDue date.

Leave the PaymentWasDue field blank for the payments of the
InitialCommssion: it relates only to recurring commissions.

This kind of approach, where you provide the full trail of what was paid
to whom when and why, is the only way to not only get the database working
correctly, but be able to demonstrate that it is working correctly, and to
fix it and correct any over/under payments if you did get it wrong.

HTH
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have to develop an access app that records commissions for selling
maintenance contracts. I started with a simple sales table but quickly ran
into trouble because sometimes the commissions are paid in full at the
time of the sale, and some times they are paid over time as long as the
maintenance contract is in effect. (Example, one sale might be a flat
commission of $10 for that sale, another commission might get paid $5.00
per month as long as the contract is in effect)

Is it proper database design to have a table that creates a separate
record for each month as long as the maintenance contract is in effect?
If so, how would I got about doing that? Or do I stick with a table that
has a single record for the sale and use some type of report logic to
display the recurring commission each month?

How is this type of situation normally handled? Thanks in advance

joe

 

[Allen Browne]

There are several screens full of code you need at that point.

In preparation, you mock up a query that selects the contracts where the
NextDue date is before some end date (typically end of month), ordered by
EmployeeID, and switch it to SQL View. Copy the SQL statement you see there
into your code, and OpenRecordset into that as the source. Then you want to
OpenRecordset into the Payment table and also the PaymentDetail table as the
targets. Walk through the source. AddNew to the Payment if it's a new
person. AddNew to the PaymentDetail until you reach a different person, and
so on.

It's too large and specific to post.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen

Wow. Thank you for the great response. I am following you on most of what
you are suggesting. Can you elaborate on how to "append the records to
your Payment and
PaymentDetail tables, using recordsets and INSERT query statements." or
provide a link that shows how to do this?

Thanks again.

- Joe

Hi Joe

You will need to be quite comfortable with VBA and the SQL view of
queries to achieve this.

For any Contract, you want fields to record the details of the contract,
and also these fields:
ContractID primary key
ContractDate Date/Time
ClientID foreign key to Client.ClientID
SoldByID foreign key to Employee.EmployeeID
InitalCommission Currency
RecurCommission Currency
RecurPeriodType Text (4) contains "d", "m", "q", or "yyyy"
RecurFrequency Number (Long) number of days/months between
commission pay'ts.
InactiveDate Date/Time date the contract ceases (blank
for current ones).
If the commision on a contract may need to be shared between multiple
employees, this all needs to be in a related table. If the commissions
are tiered scales, that's a whole other story.

For the commission payments, you want a Payment table:
PaymentID primary key
PaymentDate Date/time
EmployeeID foreign key to Employee.EmployeeID. Who was paid.
and a PaymentDetail table:
PaymentID foreign key to Payment.PaymentID
ContractID foreign key to Contract.ContractID
PaymentWasDue Date/Time. The date this payment was owed to the
employee.
Amount Currency

You can now calculate when a commission payment is next due, as:
DateAdd([RecurPeriodType], [RecurFrequency],
Nz([MaxOfPaymentDate], [ContractDate]))
where MaxOfPaymentDate is calculated as:
SELECT Max([PaymentDate]) FROM CommissionDetail
WHERE CommissionDetail.ContractID = xxx)

Now you know how to calculate the next payment date, you can program a
routine to select all the contracts that have a next due date up to the
end of the period (month)?, and append the records to your Payment and
PaymentDetail tables, using recordsets and INSERT query statements. This
provides a full history of what payments were made on what dates to which
sales representatives, and for which contracts. Additionally, it allows
the flexibility to add bonus payments or skip payments if your company
hits unusual circumstances, since the calcuation is always done based on
one period after the last PaymentWasDue date.

Leave the PaymentWasDue field blank for the payments of the
InitialCommssion: it relates only to recurring commissions.

This kind of approach, where you provide the full trail of what was paid
to whom when and why, is the only way to not only get the database
working correctly, but be able to demonstrate that it is working
correctly, and to fix it and correct any over/under payments if you did
get it wrong.

HTH

I have to develop an access app that records commissions for selling
maintenance contracts. I started with a simple sales table but quickly
ran into trouble because sometimes the commissions are paid in full at
the time of the sale, and some times they are paid over time as long as
the maintenance contract is in effect. (Example, one sale might be a flat
commission of $10 for that sale, another commission might get paid $5.00
per month as long as the contract is in effect)

Is it proper database design to have a table that creates a separate
record for each month as long as the maintenance contract is in effect?
If so, how would I got about doing that? Or do I stick with a table that
has a single record for the sale and use some type of report logic to
display the recurring commission each month?

How is this type of situation normally handled? Thanks in advance