Tracking and updating vacation

[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.

 

[Jess]

-----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.



.

 

[Jess]

Hi

If this task were mine I would probably adopt something
like the following approach.

I would add a data field to the table to represent Opening
Balance. I would have a two part update which would run
each 1st July (1) to add 40 hours for the new allocation
and (2)to run through the data for the previous year,
total the sick hours taken, and deduct them from the
total. This way, each year, you would have the new 40
hour allocation plus what was left over from time past.

After this you could backup the table before clearing out
the data. (If your employee sick leave history ever needs
to be audited or could be called into question, this is a
good idea).

At any stage during the year you could have your report
reflecting opening balance and then picking up the
individual records whose type is AS and reflecting the
balance.

HTH
Jess

 

[Van T. Dinh]

Since old [occurrences] Records are deleted, you must store the cumulative
AS credits somewhere in your database. My suggestion is to create a Table
tblSickLeaveCredit with the following Fields:

* SLCID AutoNumber PrimaryKey
* frg_EmployeeID ForeignKey from Table [employees]
* SLCAsOfDate DateTime
* SLCCumulative Double or Decimal (hours)

Probably what you can do is to create one Record for each current employee
with the SLCAsOfDate 01/Jul (after the 40 hours credit of each year). You
can combine the cumulative calculation and the addition of 40 hours for the
year into one step. I even go further and perform the deletion of old
[occurrences] Records on 01/Jul retaining up to 18 months worth of Records
if required. Thus, you only need to do major maintenance once a year.

I am sure you can fit the partial year credit for new Employees into this
structure also.

This way, you can always work out the current SLC starting with the last
available Record for the Employee in the tblSickLeaveCredit.

 

[ted Hall]

Van,

Thanks for the help, and that is what I have been trying
to do. I have a separate table that I wanted to store
employee sick time in. My problem is updating that
table. I would like to update it with an update query,
perhaps just run this query once a year (july 1) which
would replace the vacation hours in the table with what
hours were not used + the new 40 hrs. I can't get the
update query to work, I get an error that says it must be
an updatable query which doesn't make sense since I am
updating a table.

-----Original Message-----
Since old [occurrences] Records are deleted, you must store the cumulative
AS credits somewhere in your database. My suggestion is to create a Table
tblSickLeaveCredit with the following Fields:

* SLCID AutoNumber PrimaryKey
* frg_EmployeeID ForeignKey from Table [employees]
* SLCAsOfDate DateTime
* SLCCumulative Double or Decimal (hours)

Probably what you can do is to create one Record for each current employee
with the SLCAsOfDate 01/Jul (after the 40 hours credit of each year). You
can combine the cumulative calculation and the addition of 40 hours for the
year into one step. I even go further and perform the deletion of old
[occurrences] Records on 01/Jul retaining up to 18 months worth of Records
if required. Thus, you only need to do major maintenance once a year.

I am sure you can fit the partial year credit for new Employees into this
structure also.

This way, you can always work out the current SLC starting with the last
available Record for the Employee in the tblSickLeaveCredit.

--
HTH
Van T. Dinh
MVP (Access)

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.

.

 

[Van T. Dinh]

Not that it matters but I suggested adding a new Record for each Employee on
the 1st of Jul every year. Your post seems to say that you want to modify
Records.

Perhaps, it is better for audit purposes to have a Record per Employee per
year. Also, it is easier to handle partial year for new Employees.

Regardless, you should be able to add Record (or update Records) in the
tblSickLeaveCredit from Records in the Tables [employees] and [occurrences].

Check the Queries chapter of your Access book and see if you can make the
Query work.