30 reward dollars per calendar month.

[NewMexicoNovice]

Colleagues,

Not sure where to begin to learn/read up on this...

...I am building a simple employee rewards database. Supervisors should have only 30 reward dollars that they can give out every month. Managers should only have 200 reward dollars that they can give out in a calendar month.

How/where do learn how to construct this kind of cap for each calendar month? Any insights and direction are really appreciated.

[Dal Jeanis]

It depends on the rest of your application architecture. Start off with listing all the information that you need to keep, and what reports you'll want to run, and who will be using the application, and how. You'll need staff records, boss-awards-available records, and money-awarded records at a minimum. Each boss should be able to see a screen or report with what rewards he has currently available, and to see summaries and detail of what awards he has granted previously, maybe in listing and crosstab formats (by month and staff name).

Just off the top of my head, I'd say...

When a boss awards money to a staffer, create an award record and transfer the money from the boss's awards-available record to the award record. The award record will need the ID of the awarding boss, so that multiple bosses can give the money to the same person in the same month if warranted.

Not sure how you're getting the bonus to the staffers, whether it will be in their paycheck, in which case you'll need a report for payroll, or if they get it some other way.

SOME PROCESS IDEAS

One week before the monthend, send an email reminder notice to each supervisor and manager of how much unused money they have, and the date on which that money will be "swept".

At the beginning of each calendar month, run a job (update query) that (A) zeroes out any unused money and (B) gives each supervisor and manager their monthly allotment for the new month.

When you sweep the awards-available records at the end of the month, for the remainder of the money, create an award record with a null staff id, or the staff id of a "NOBODY" staff record, or have a separate kind of record or separate table for money-not-awarded, so you can balance the books against the amount that SHOULD have been awarded each month.

[NewMexicoNovice]

Excellent. Dal, thanks so much for being gracious with my newbie knowledge base. I've thought through your recommendations all day long and they give me something quite exciting to research over the weekend. I really like the idea behind the boss-awards available record to the award record AND the manual process idea of running a query to zero out the balance. Again, thanks so much for taking the time to help me.

Best,
Burgess