Running total table

[Ted Hall]

I have a database with employee information in it. I
would like to include tracking employees Sick time in a
separate table. Each July 1st every employee gets 40 hrs
of sick time. If they do not us it, it accrues to the
next year and so on. I am trying to figure how I can
have access track this. The database is accessed by over
20 people entering employee info. I need a table that
will have a running total. Whenever an employees takes a
sick day, it is entered into an occurence table. I need
access to deduct those hours from the employees total
hours. I don't want it to be deducted automatically,
because if the sick day changes to another type of time
off, the hours would have to be manually put back into
the table. With over 300 employees this causes a time
tracking nightmare. Also absent sick data is removed
from the database every 2 years, so I would need to have
a table that would store yearly totals used. I would
ultimately like to create a report to give to each
employee that would list an employees sick days, and show
how much sick time they have left.

Has anyone developed a database like this or have any
suggestions? Thanks in advance.

 

[Norman Yuan]

Simply add another table to hold employee's sick time record (not total sick
time, though). It may look like:

Employee Table SickTime Table
---------------- ------------------
EmployeeID RecordID
LastName EmployeeID
FirstName SickDate
.... SickTime
.... Note
.... ....

Obviously, you need to establish a ONE-to-MANY relationship between Employee
Table and SickTime Table.

Since the SickTime table keep track of employees' sick time, it is easy to
query toatl sick time taken by an employee in any given time period (You
never save a total number in a table if it can be derived (calculated) from
existing data in database).

 

[David Wilson]

I would suggest adding a field in the current employee record that has sick
time balance. Adding another table just requires additional maintenance
(such as when a new employee would be added). A good reason not to do this
is if you are not the primary "DBA" for the employee table. Will one
employee ever have more than one sick time balance (i.e. Many sick time
balances)? If not, make it a field.

Then the trick is to have Access add the occurance to the occurance table
and update the balance in one action by the user.

The best way to do that depends on a lot of factors. I would first try a
macro that opens a query that calculates the new balance. This may require
adding a true/false flag to the occurance table to indicate if the
occurrence has been deducted from the total.