Tracking Employee Sick Time

[Ted Hall]

I am having a problem trying to have an access database
track employees sick time. On July 1 every employee is
given 40 hours of sick time. They keep that time forever
or until they use it. For example employee 1 has 40
hours on july 1, 2002. Prior to july 1, 2003 he uses 16
hours. On july 1, 2003 he gets 40 more hours and keeps
the unused hours from the previous year for a total of 64
hours remaining. Right now I have an employee table that
lists all employee information.
[employees]
employeesid
lastname
firstname
MI
DOH
EMP#
Department

I have a second table [occurences] that lists what an
employee did every day they were scheduled to work.
[occurences]
employeesid
date
job function code
hours

The job function codes are 2 character abreviations for
the work class. IE: PA for put away, AS for Absent Sick.

After all of the employees information has been entered
for the day I want to be able to generate a report for
each employee that shows how much sick time is left. My
biggest problem is that we have over 300 employees, and
entering several lines of work data for each employee
every day, the table is getting large. Since we don't
need this data for longer than a year, each year I go in
and clean out old data in the [occurence] table. On
January 1, 2004 I will remove data older than Jan 1,
2003. Does anyone have an Idea how I can track sick
time? Several people enter occurence data into the
database everyday, and I would like to do this with as
little manual work as possible. Thanks.

 

[Liz]

I have done something very similiar dealing with Vacation
time, and how much has been used.
I actually did this using a form and subform. I found
doing it in a form is easier to deal with when you have
numerous employees. I set the form up with fields for the
Employee ID, Employee Name, SumofVacation Taken,
SumofVacation Available and Total Available Vacation
(Sumof Vacation taken-SumofVacation Available). You would
substitute your sick time hours.The Form was based on a
query where the two fields were totalled.
The subform contained the following fields: Employee,
Vacation Hours and Vacation Taken.
The subform was based on the table. That way new data
could be entered into the subform .
I had two fields to track vacation time in the table:
Vacation available and Vacation taken. The fields could
be added and subtracted in the query to get remaining
vacation time.
I did all the tracking within one table.
Since you would be running the MAIN FORM off a query to
total the hours, you need only add the initial 40 hours to
each employee as a 1 time occurrence in your table.
Hope this helps. If I can assist with further details,
please post back to this newsgroup.

-----Original Message-----
I am having a problem trying to have an access database
track employees sick time. On July 1 every employee is
given 40 hours of sick time. They keep that time forever
or until they use it. For example employee 1 has 40
hours on july 1, 2002. Prior to july 1, 2003 he uses 16
hours. On july 1, 2003 he gets 40 more hours and keeps
the unused hours from the previous year for a total of 64
hours remaining. Right now I have an employee table that
lists all employee information.
[employees]
employeesid
lastname
firstname
MI
DOH
EMP#
Department

I have a second table [occurences] that lists what an
employee did every day they were scheduled to work.
[occurences]
employeesid
date
job function code
hours

The job function codes are 2 character abreviations for
the work class. IE: PA for put away, AS for Absent Sick.

After all of the employees information has been entered
for the day I want to be able to generate a report for
each employee that shows how much sick time is left. My
biggest problem is that we have over 300 employees, and
entering several lines of work data for each employee
every day, the table is getting large. Since we don't
need this data for longer than a year, each year I go in
and clean out old data in the [occurence] table. On
January 1, 2004 I will remove data older than Jan 1,
2003. Does anyone have an Idea how I can track sick
time? Several people enter occurence data into the
database everyday, and I would like to do this with as
little manual work as possible. Thanks.
.

 

[Ted Hall]

-----Original Message-----
I have done something very similiar dealing with Vacation
time, and how much has been used.
I actually did this using a form and subform. I found
doing it in a form is easier to deal with when you have
numerous employees. I set the form up with fields for the
Employee ID, Employee Name, SumofVacation Taken,
SumofVacation Available and Total Available Vacation
(Sumof Vacation taken-SumofVacation Available). You would
substitute your sick time hours.The Form was based on a
query where the two fields were totalled.
The subform contained the following fields: Employee,
Vacation Hours and Vacation Taken.
The subform was based on the table. That way new data
could be entered into the subform .
I had two fields to track vacation time in the table:
Vacation available and Vacation taken. The fields could
be added and subtracted in the query to get remaining
vacation time.
I did all the tracking within one table.
Since you would be running the MAIN FORM off a query to
total the hours, you need only add the initial 40 hours to
each employee as a 1 time occurrence in your table.
Hope this helps. If I can assist with further details,
please post back to this newsgroup.

-----Original Message-----
I am having a problem trying to have an access database
track employees sick time. On July 1 every employee is
given 40 hours of sick time. They keep that time forever
or until they use it. For example employee 1 has 40
hours on july 1, 2002. Prior to july 1, 2003 he uses 16
hours. On july 1, 2003 he gets 40 more hours and keeps
the unused hours from the previous year for a total of 64
hours remaining. Right now I have an employee table that
lists all employee information.
[employees]
employeesid
lastname
firstname
MI
DOH
EMP#
Department

I have a second table [occurences] that lists what an
employee did every day they were scheduled to work.
[occurences]
employeesid
date
job function code
hours

The job function codes are 2 character abreviations for
the work class. IE: PA for put away, AS for Absent Sick.

After all of the employees information has been entered
for the day I want to be able to generate a report for
each employee that shows how much sick time is left. My
biggest problem is that we have over 300 employees, and
entering several lines of work data for each employee
every day, the table is getting large. Since we don't
need this data for longer than a year, each year I go in
and clean out old data in the [occurence] table. On
January 1, 2004 I will remove data older than Jan 1,
2003. Does anyone have an Idea how I can track sick
time? Several people enter occurence data into the
database everyday, and I would like to do this with as
little manual work as possible. Thanks.
.

.
Liz,

My problem is twofold. One when I remove the old years
data, those hours are no longer there to be subtracted
from the total hours available. Also, I haven't been
able to come up with a way to update the table that
contains the vacation taken and remaining. I also don't
want it to happen automatically, because after an AS is
entered, 2-3 days later it may be changed to V for
vacation, and then I would need a way to put those hours
back in. Thanks for your help.

 

[Liz]

Ted,
Are looking just to track the sick time, or the vacation
time and others as well?

-----Original Message-----

-----Original Message-----
I have done something very similiar dealing with Vacation
time, and how much has been used.
I actually did this using a form and subform. I found
doing it in a form is easier to deal with when you have
numerous employees. I set the form up with fields for the
Employee ID, Employee Name, SumofVacation Taken,
SumofVacation Available and Total Available Vacation
(Sumof Vacation taken-SumofVacation Available). You would
substitute your sick time hours.The Form was based on a
query where the two fields were totalled.
The subform contained the following fields: Employee,
Vacation Hours and Vacation Taken.
The subform was based on the table. That way new data
could be entered into the subform .
I had two fields to track vacation time in the table:
Vacation available and Vacation taken. The fields could
be added and subtracted in the query to get remaining
vacation time.
I did all the tracking within one table.
Since you would be running the MAIN FORM off a query to
total the hours, you need only add the initial 40 hours to
each employee as a 1 time occurrence in your table.
Hope this helps. If I can assist with further details,
please post back to this newsgroup.

-----Original Message-----
I am having a problem trying to have an access database
track employees sick time. On July 1 every employee is
given 40 hours of sick time. They keep that time forever
or until they use it. For example employee 1 has 40
hours on july 1, 2002. Prior to july 1, 2003 he uses 16
hours. On july 1, 2003 he gets 40 more hours and keeps
the unused hours from the previous year for a total of 64
hours remaining. Right now I have an employee table that
lists all employee information.
[employees]
employeesid
lastname
firstname
MI
DOH
EMP#
Department

I have a second table [occurences] that lists what an
employee did every day they were scheduled to work.
[occurences]
employeesid
date
job function code
hours

The job function codes are 2 character abreviations for
the work class. IE: PA for put away, AS for Absent Sick.

After all of the employees information has been entered
for the day I want to be able to generate a report for
each employee that shows how much sick time is left. My
biggest problem is that we have over 300 employees, and
entering several lines of work data for each employee
every day, the table is getting large. Since we don't
need this data for longer than a year, each year I go in
and clean out old data in the [occurence] table. On
January 1, 2004 I will remove data older than Jan 1,
2003. Does anyone have an Idea how I can track sick
time? Several people enter occurence data into the
database everyday, and I would like to do this with as
little manual work as possible. Thanks.
.

.
Liz,

My problem is twofold. One when I remove the old years
data, those hours are no longer there to be subtracted
from the total hours available. Also, I haven't been
able to come up with a way to update the table that
contains the vacation taken and remaining. I also don't
want it to happen automatically, because after an AS is
entered, 2-3 days later it may be changed to V for
vacation, and then I would need a way to put those hours
back in. Thanks for your help.
.

 

[Ted hall]

-----Original Message-----
Ted,
Are looking just to track the sick time, or the vacation
time and others as well?

-----Original Message-----

-----Original Message-----
I have done something very similiar dealing with Vacation
time, and how much has been used.
I actually did this using a form and subform. I found
doing it in a form is easier to deal with when you have
numerous employees. I set the form up with fields for the
Employee ID, Employee Name, SumofVacation Taken,
SumofVacation Available and Total Available Vacation
(Sumof Vacation taken-SumofVacation Available). You would
substitute your sick time hours.The Form was based on a
query where the two fields were totalled.
The subform contained the following fields: Employee,
Vacation Hours and Vacation Taken.
The subform was based on the table. That way new data
could be entered into the subform .
I had two fields to track vacation time in the table:
Vacation available and Vacation taken. The fields could
be added and subtracted in the query to get remaining
vacation time.
I did all the tracking within one table.
Since you would be running the MAIN FORM off a query to
total the hours, you need only add the initial 40

hours
to

each employee as a 1 time occurrence in your table.
Hope this helps. If I can assist with further details,
please post back to this newsgroup.
-----Original Message-----
I am having a problem trying to have an access database
track employees sick time. On July 1 every employee is
given 40 hours of sick time. They keep that time forever
or until they use it. For example employee 1 has 40
hours on july 1, 2002. Prior to july 1, 2003 he uses 16
hours. On july 1, 2003 he gets 40 more hours and keeps
the unused hours from the previous year for a total

of
64

hours remaining. Right now I have an employee table that
lists all employee information.
[employees]
employeesid
lastname
firstname
MI
DOH
EMP#
Department

I have a second table [occurences] that lists what an
employee did every day they were scheduled to work.
[occurences]
employeesid
date
job function code
hours

The job function codes are 2 character abreviations for
the work class. IE: PA for put away, AS for Absent Sick.

After all of the employees information has been entered
for the day I want to be able to generate a report for
each employee that shows how much sick time is left. My
biggest problem is that we have over 300 employees, and
entering several lines of work data for each employee
every day, the table is getting large. Since we don't
need this data for longer than a year, each year I go in
and clean out old data in the [occurence] table. On
January 1, 2004 I will remove data older than Jan 1,
2003. Does anyone have an Idea how I can track sick
time? Several people enter occurence data into the
database everyday, and I would like to do this with as
little manual work as possible. Thanks.
.

.
Liz,

My problem is twofold. One when I remove the old years
data, those hours are no longer there to be subtracted
from the total hours available. Also, I haven't been
able to come up with a way to update the table that
contains the vacation taken and remaining. I also don't
want it to happen automatically, because after an AS is
entered, 2-3 days later it may be changed to V for
vacation, and then I would need a way to put those hours
back in. Thanks for your help.
.

.
Just the sick time. I can already track the Vacation

time because the employees get that on Jan. 1, and it
doesn't carry over from year to year. It all has to be
gone by Dec. 31.

 

[Liz]

To track the sick time you can do an IIF statement in the
query that runs the form to just total the Sick Time like
this:
AS Hours: Sum(IIf([type of vacation]="AS",[vacation
hrs],Null))

Then to get the total of hours taken:
AS Hours Taken: Sum(IIf([type of vacation]="AS",[vacation
taken],Null))

Where field [type of vacation] is whatever you call that
field for tracking the type of time off.

This will track in the form and you aren't taking any
hours out of the table. If you have to change a type from
V to AS it will update the data - in the form - through
the query.
I hope I haven't made this sound complicated. It really
isn't .... just a few steps.

-----Original Message-----

-----Original Message-----
Ted,
Are looking just to track the sick time, or the vacation
time and others as well?

-----Original Message-----

-----Original Message-----
I have done something very similiar dealing with
Vacation
time, and how much has been used.
I actually did this using a form and subform. I found
doing it in a form is easier to deal with when you have
numerous employees. I set the form up with fields for
the
Employee ID, Employee Name, SumofVacation Taken,
SumofVacation Available and Total Available Vacation
(Sumof Vacation taken-SumofVacation Available). You
would
substitute your sick time hours.The Form was based on a
query where the two fields were totalled.
The subform contained the following fields: Employee,
Vacation Hours and Vacation Taken.
The subform was based on the table. That way new data
could be entered into the subform .
I had two fields to track vacation time in the table:
Vacation available and Vacation taken. The fields could
be added and subtracted in the query to get remaining
vacation time.
I did all the tracking within one table.
Since you would be running the MAIN FORM off a query to
total the hours, you need only add the initial 40 hours
to
each employee as a 1 time occurrence in your table.
Hope this helps. If I can assist with further details,
please post back to this newsgroup.
-----Original Message-----
I am having a problem trying to have an access database
track employees sick time. On July 1 every employee is
given 40 hours of sick time. They keep that time
forever
or until they use it. For example employee 1 has 40
hours on july 1, 2002. Prior to july 1, 2003 he uses
16
hours. On july 1, 2003 he gets 40 more hours and keeps
the unused hours from the previous year for a total of
64
hours remaining. Right now I have an employee table
that
lists all employee information.
[employees]
employeesid
lastname
firstname
MI
DOH
EMP#
Department

I have a second table [occurences] that lists what an
employee did every day they were scheduled to work.
[occurences]
employeesid
date
job function code
hours

The job function codes are 2 character abreviations for
the work class. IE: PA for put away, AS for Absent
Sick.

After all of the employees information has been entered
for the day I want to be able to generate a report for
each employee that shows how much sick time is left.
My
biggest problem is that we have over 300 employees, and
entering several lines of work data for each employee
every day, the table is getting large. Since we don't
need this data for longer than a year, each year I go
in
and clean out old data in the [occurence] table. On
January 1, 2004 I will remove data older than Jan 1,
2003. Does anyone have an Idea how I can track sick
time? Several people enter occurence data into the
database everyday, and I would like to do this with as
little manual work as possible. Thanks.
.

.
Liz,

My problem is twofold. One when I remove the old years
data, those hours are no longer there to be subtracted
from the total hours available. Also, I haven't been
able to come up with a way to update the table that
contains the vacation taken and remaining. I also don't
want it to happen automatically, because after an AS is
entered, 2-3 days later it may be changed to V for
vacation, and then I would need a way to put those hours
back in. Thanks for your help.
.

.
Just the sick time. I can already track the Vacation

time because the employees get that on Jan. 1, and it
doesn't carry over from year to year. It all has to be
gone by Dec. 31.
.