If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Mark Commission as Paid
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 |
#2
|
|||
|
|||
Mark Commission as Paid
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 "BMoroneso" wrote in message ... 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 |
#3
|
|||
|
|||
Mark Commission as Paid
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. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "BMoroneso" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|