Calender as a Database

[Russell Salter]

I need to record people's sickness, holiday, maternity
leave etc at work. I was thinking of having a different
table for each type of abscence but my question is : would
I have to have each day of the year as a separate field in
my database or is there an easier/better way to go about
it? In theory, I wouldn't just cover 1 year (365 fields?!)
in each table, there could be the need to cross over years
in which case wouldn't I need to have more than 365 fields?
Any ideas people might have would be gratefully received.

 

[Mark]

Obviously I don't know all the details, but it would seem
better to have a single Absence table which includes a
field to specify the type of absence.(S=Sickness,
M=Maternity etc)

 

[Randy Harris]

I need to record people's sickness, holiday, maternity
leave etc at work. I was thinking of having a different
table for each type of abscence but my question is : would
I have to have each day of the year as a separate field in
my database or is there an easier/better way to go about
it? In theory, I wouldn't just cover 1 year (365 fields?!)
in each table, there could be the need to cross over years
in which case wouldn't I need to have more than 365 fields?
Any ideas people might have would be gratefully received.

A separate field for each day would be a very bad idea, not to mention the
fact that Access only supports 255 fields in a table. You should probably
set up 3 tables. People, AbscenceCauses, and Abscenses. The second is a
simple lookup table, which will make it easy to change the Causes (which you
will!)

1)People
PersonID (primary key, probably an autonumber)
Name
Address
City
State
EmployeeNumber
WhateverElse you wish to keep

2)AbscenceCauses
CauseID (primary key, probably an autonumber)
Cause
AnythingElse you wish to keep

3)Abscences
AbscenceID (primary key, probably an autonumber)
PersonID
CauseID
AbscenceDate
WhateverElse (perhaps approved?)

Setting it up in this fashion will permit you to easily do reports and
graphs - Abscences per time period, Abscences by Cause, Abscences by
Employee, Abscences before holidays?, whatever

HTH
Randy

 

[Terry]

Russell,
I have designed a similar database at my job. I went with
a 'transaction' approach and is working well.
Accrued time such as monthly vacation and sick are in the
same table as well as time used. At the beginning of each
calendar year, I run a quick VBA routine that sets up the
next years known accruals - vacation time, sick time,
birthday. When I am running reports or displaying data on
a form, I filter it for that day backwards. The tables
used are:

tblEmployees
EmpID
EmpLastName
EmpDOH
EmpDOB
etc.

Transactions
TransID
TransTypeID
TransHours
TransDate
etc.

TransactionType
TransTypeID
TransDesc
TransMultiplier (1 or -1)

The transaction type table has the multiplier for
calculating the time.

HTH

Terry