Mark Commission as Paid

[BMoroneso]

Hi Everyone,
I'm creating a database that tracks revenue received, commission structures
related to that revenue, and when those commissions have been paid. It also
does a bit of calculating - commission due, and revenue/commission
comparisons across/between companies, divisions, sales people, etc.
Most of this has been handled already. My snag here is as follows:
I want to create a form where a user can look at a list of commissions that
have not been paid, filter that list by date revenue was received, and then
mark those items as paid with a user-specified date.
I have a table - tblRectComm - that tracks each incidence of a receipt and
the matching commission structure(s). I believe that is the best place to
store the "Date Paid" information. The problem is in setting up the form and
then updating the table.
Thanks in advance for your help!
Becky

 

[Jeanette Cunningham]

Becky,
in your description below

I'm creating a database that tracks revenue received, commission
structures
related to that revenue, and when those commissions have been paid.

You refer to >when those commissions have been paid.
I would think that would be the correct field to store "a user-specified
date" or the "Date Paid" information.

Sounds like you are trying to match up unpaid commissions with a list of
receipts.
Your form needs a filter with a combo with a list of updaid commissions
after the user chooses a commission from the combo, the form shows that
record
Can you base the form on the table with commission info or on a query that
includes commission info together with the data paid?

The user can choose a date from a second combo showing dates of revenue
receipts to fill in the date paid field.

Jeanette Cunningham

 

[Allen Browne]

Becky, can I suggest that you track not just that a commission has been paid
(yes/no field), but the record when the commission was paid.

Typically this involves writing code that is run at the end of the month.
You have a Commission table, with fields like this:
ComissionID AutoNumber primary key
StaffID who got paid
BatchID Number (indicates this entry is part of a batch.)

You also have a Batch table, with fields:
BatchID Autonumber primary key
BatchDate Date/Time

In your sales detail table (line items of the invoice/receipt/whatever),
include a CommissionID field that is blank until the commisson has been
paid.

So, at the end of the month, you run the code. It identifies which sales
people are due commission, and creates a record for each one of them in the
Commission table. It then assigns the new CommissionID to the records in the
sales detail table that have not had any commission paid yet.

The structure can get more complex than that if there could be cases where a
sales is shared between 2 or more sales people, or if the sales commission
figures are tiered, or have other complexities, but that's a starting point.