Trying to make a database for working hours versus absent from work

[Jan T.]

I am quit new to Access but will try to make a database for all employees.
It
should contain who works full time, part time and I want to register every
one that is absent every day. Finally it shall give information about
number of days an employee has been sick, or absent because of other
reasons. How should I design the tables and datastructure?

I want statistics from the database on how many % of total working hours
my employees are sick during a year and how many days did they take off.
If a part time worker works 50% and is sick for two days in a month that
should be appr. (9% / 2 = ) 4,5% absense for that particular month an so on.

My idéa is to have a form with a listbox that shows all the employees that
are supposed to be at work that day (daily rutine). Then multiselect those
that are absent that day. NB, the list box should only show the persons who
are suppost to be at work that day. That is, if one person only works two
days a week, say monday and wednesday, he would not appear in the listbox
the other days.

If I am right I would have an Employee table (with EmplId as an Autonumber)
and
one field for every workday for one regular week containing information
about how
many workinghours (from 0 to 7.5 hrs) that person is expected to work. I am
thinking of making the work day fields as properties of the employee.

So far so good? Then I would probably need to register those that are absent
in
an other table with EmplId and weekday and number of hours absent?

I you still follow me, is this an good idé or does someone have a better
idéa?

PS I am using WinXP, Access 2003. Is this the right news group or should I
rather
try table design news group for this case?

Thanks in advance for any help and suggestion!

Regards Jan.

 

[Ed Metcalfe]

I am quit new to Access but will try to make a database for all employees.
It
should contain who works full time, part time and I want to register every
one that is absent every day. Finally it shall give information about
number of days an employee has been sick, or absent because of other
reasons. How should I design the tables and datastructure?

I want statistics from the database on how many % of total working hours
my employees are sick during a year and how many days did they take off.
If a part time worker works 50% and is sick for two days in a month that
should be appr. (9% / 2 = ) 4,5% absense for that particular month an so
on.

My idéa is to have a form with a listbox that shows all the employees that
are supposed to be at work that day (daily rutine). Then multiselect those
that are absent that day. NB, the list box should only show the persons
who
are suppost to be at work that day. That is, if one person only works two
days a week, say monday and wednesday, he would not appear in the listbox
the other days.

If I am right I would have an Employee table (with EmplId as an
Autonumber) and
one field for every workday for one regular week containing information
about how
many workinghours (from 0 to 7.5 hrs) that person is expected to work. I
am
thinking of making the work day fields as properties of the employee.

So far so good? Then I would probably need to register those that are
absent in
an other table with EmplId and weekday and number of hours absent?

I you still follow me, is this an good idé or does someone have a better
idéa?

PS I am using WinXP, Access 2003. Is this the right news group or should I
rather
try table design news group for this case?

Thanks in advance for any help and suggestion!

Regards Jan.

Jan,

Off the top of my head I would look at including the following tables in
your database:

Employees
EmployeeStandardHours
EmployeeAbsences
EmployeeAbsenceTypeCodes
PublicHolidays

This would work assuming you only want to explicitly record absences - if
there is no absence recorded you assume the person was in. Hopefully you can
see how these would relate together and what fields live in each. I think
this would give you the basis of what you want.

Ed Metcalfe.

 

[Jan T.]

I will try to make the tables and see how it works.

Thanks very much Ed for your advice and help.

Regards
Jan

 

[Ed Metcalfe]

I will try to make the tables and see how it works.

Thanks very much Ed for your advice and help.

Regards
Jan

Jan,

You're welcome!

Post back if you run into any difficulties.

Ed Metcalfe.