Tracking sick time

[Ted Hall]

I am trying to track sick time taken by employees. July
1 every employees gets 40 hours of sick time. Through
the year when time is taken it is entered into an
occurence table. I would like to have a report that
tells each employee how much time they have used and
left. The problem is that if an employee does not use
the time it carries over to the next year. We have a lot
of employees, and not only do we enter the sick time into
the occurence table, but we enter everything the employee
does. Currently the occurence table has 125,000+
entries. To kee it managable I will only keep two years
of date in the occurence table. Anybody have an idea how
to track employees sick time? Thanks.

 

[Joseph Meehan]

Well identifying the best answer would require a few more facts.
However let me make a few suggestions.

First 125,000 records is not may. I don't think you will need to delete
anything soon. Deleting records would require some adjustment to net out
all the old entries to make one "prior" entry to keep the total accurate.

Second you do the math in a query, or maybe a separate query for each
employee or any of a number of other methods, depending on the output you
need.

So a couple of questions. How many employees? How often do they
change? Are you keeping a list of the employees anywhere else? How do you
need/want the current available sick leave to be displayed?

 

[Ted Hall]

First to keep the database running somewhat fast over the
network I do like to delete the old entries. I about
about 20 users that access the data base at various times
throughout the day. I backup the database, then sort the
occurence table by year, and then delete the old year. (
In 2004 I will delete 2002). So yes I would like to net
out the old data prior to removing it.
Yes, I will do the math in a query, because all I need is
a report to give to the employees. We have 350
employees, and we probably add about 25 per year. As old
employees leave, I don't delete them I change the
department they are in to X. All of my employees
information is stored in the employees table, with the
occurences table linked to the employees table by
employeesid. I hope this explains things so that you can
help. Thanks.

 

[Tamara]

Ted,

Have you thought about entering the employees information in excel. What I
would do is put all the employees information on one worksheet, but you may
copy and paste the name and employee account number to another wooksheet
within that book, within this worksheet you should enter the employee
PTO/SICK TIME and also have a colum for SICK DAYS USED where you can enter
the exact date that the employee used. for example Name Date of
hhihHire Dept. Rank
Tamara *06/21/02,Elections 1550 Hourly *01547
*12/23/02-Assessors- 1/23/04 goes to 12 hrs./Month-1 year
Pay Per Pay Per. Time (+) on Pay Personal Comp./Or
Begins Ends Accrued Check Balance Used (-1) Day ELB Other
Balance as of 12/20/02 0 0
12/21/2002 1/3/2003 * + 8 12/25/2002 0 * +8
1/4/2003 1/17/2003 0
1/18/2003 1/31/2003 * + 8 1/25/2003 8 hrs.
2/1/2003 14-Feb 8 hrs.
2/15/2003 28-Feb * + 8 2/25/2003 16 hrs. *8 w/out pay
3/1/2003 14-Mar 16 hrs.
3/15/2003 28-Mar * + 8 3/25/2003 24 hrs.
3/29/2003 11-Apr 24 hrs.
4/12/2003 25-Apr * + 8 4/25/2003 32 hrs.
4/26/2003 9-May 32 hrs. *1 w/o pay
5/10/2003 5/23/2003 32 hrs. *1 w/o pay
5/24/2003 6/6/2003 * + 8 5/25/2003 40 hrs.
6/7/2003 6/20/2003 40 hrs.
6/21/2003 7/4/2003 * + 8 6/25/2003 43.50 hrs. 4.50 PTO May begin to
use PTO- 6 month-
7/5/2003 7/18/2003 43.50 hrs. probation is expired
7/19/2003 8/1/2003 * + 8 7/25/2003 51.50 hrs.
8/2/2003 8/15/2003 51.50 hrs.
8/16/2003 8/29/2003 * + 8 8/25/2003 59.50 hrs.
8/30/2003 9/12/2003 56.50 hrs. 3 PTO *-8=0
9/13/2003 9/26/2003 * + 8 9/25/2003 64.50 hrs.
9/27/2003 10/10/2003 64.50 hrs.
10/11/2003 10/24/2003 64.50 hrs.
10/25/2003 11/7/2003 * + 8 10/25/2003 72.50 hrs.
11/8/2003 11/21/2003
11/22/2003 12/5/2003 * + 8 11/25/2003
12/6/2003 12/19/2003 ***********
12/20/2003 1/2/2004 NEW YEAR-2004 BEGINS. goes to 12 hrs. PTO on
1/25/04 (1 yr.anniversary)

 

[Joseph Meehan]

I hope someone who has done all the work can offer some specific advice.
I have been working on a system to do this and vacation and personal leave
and a few other things, but a lot of other things have come first.

I suggest that you really should be able to run it over a LAN with a lot
more data than you seem to have with no performance problems. Are you using
front end/back end configuration? Do you regularly repair and compact the
database?

It sounds like you have the basics down.

I suggest you use a SUM query and group on user ID to get the numbers
you want. Use one field for both the annual addition and for the usage
making one a positive number and one negative. There are several ways you
can insure proper entry.

Does that help point you in a usable direction?

I would not be surprised if someone who has had the time to work on
something like this may recommend a better approach. If I had the time I
might end up doing it some other way myself. But this is one approach.