many types of absences

[Nicola M]

Hi all! Access 2003.
I need suggestions, advices and/or tips to solve a problem raises during the
design phase of my DB. this DB will be used to manage human resources in a
farm. All employees can go on vacation (and it is enough natural) but they
can use several types of benefits like short absence (1 day to recovered in
the future), holidays (a different amount of days used all in one time),
workshop (from 2 till 4 days to attend training and improving activities
outside the farm). I have a lot of doubto about the best way to manage this
section because I will have make available a simple report to show the
absences situation with different level of detsail (Total days regardless the
type of absence, total days divided par type of absence and so on). In the
beginning I thought a number of tables one for each type of absence plus one
containing only the first and the last days of absence. Working hard on
queries should obtain what I want. But I'm not sure.
Here the reason of my request.

thank you all in advance.

Have a wonderful and happy new year

nicola

 

[Tom van Stiphout]

On Tue, 30 Dec 2008 16:40:01 -0800, Nicola M

tblBenefits
BenefitID autonumber PK
BenefitName text255 unique index
MinDurationDays integer? single?
MaxDurationDays integer? single?

tblBenefitTaken
EmployeeID long int PK FK
BenefitID long int PK FK
StartDate datetime PK
DurationDays integer? single?

Don't worry too much about forms and reports at this stage; first make
sure you have a solid db design.
Don't forget to use the relationships window to draw RI links between
the tables, and enforce them.

-Tom.
Microsoft Access MVP

 

[Fred]

Couple of additional notes. (We use an Access app that I wrote to do this at
our company.

Our version of that "BenefitsTaken" table is "BenefitPostings" table, and we
post accruals of vacation and sick time (mathematicaly opposite sign of
benefits used). So we can generate reports that show their "available
balance" of each of these types of days, plus an itemization of accruals and
usages of those types of benefits. We also use non-integer number fields
to accomodate partial days. Our PK is just a Posting_ID autonumber because
we can have two postings on the same date.