L
Leslie Isaacs
Hello All
I have to create a facility within an existing 'Employee' database to manage
employee sickness absence. The data to be input will be the employee's name,
and the start and end dates of any sickness absence periods. These dates,
together with some other 'standing' data held in the existing 'employee'
table, will be used to calculate certain values for each day during the
sickness absence period: essentially, each day during the sickness absence
period will have to be annotated as being of one of 3 'types' (denoted as
"W", "S" or "N"). In addition, each day during the sickness absence period
will be assigned by the user to a 'pay period' (there is an existing table
of 'pay periods'). The rule that determines the 'type' will take account of
the length of the absence period, the proximity of other absence periods for
that employee, and certain other data held about the employee in the
'employee' table. Once calculated it is important that the 'type', and the
'pay period', that are assigned to each day during an absence period are
saved so that their values can be retrieved in the future. They cannot
always be re-calculated because some of the data used in the calculations
may change (primarily the data range of the sickness absence itself) - but
the results of the original calculation must be retained.
The main output needs to be a report showing, for a selected employee and
selected date range, every date between the selected date range, with each
date being displayed either with its 'sickness type' etc. if that date IS
during one of the employees absence periods, or with no 'type' etc. if it is
NOT during one of the employees absence periods. The actual layout of the
report must be to show the absence 'type' (in the case of absence dates), or
a blank field (in the case of other dates) for complete weeks horizontally -
e.g. with field names:
"Sun", "Mon", "Tue", "Wed", Thur", "Fri", "Sat", "Week ending"
the report might look like:
W W S S N N 26/07/08
S S N N N N N 02/8/08
07/8/08
14/08/08
N N N N S S N 21/8/08
The first row above would represent one blank representing 20/08/08, two
"W"s representing 21/08/08 and 22/08, two "S"s representing 23/08/08 and
24/08/08 and two "N"s representing 25/08/08 and 26/08/08. The third and
forth rows represent week with 7 'blanks' - i.e. these weeks contain no
dates that fall within any sickness absence period for the employee.
The assigned 'pay period' would not be included on this report.
My initial plan was to have a table 'sickness_periods', containing just the
employee name and the start and end dates of each sickness period, plus a
table 'sickness_days', containing the results of the calculations. Now I am
wondering whether I shouldn't bother with the first 'sickness_periods'
table, and instead just enter the data range into two unbound fields on the
data entry form, do the calculations and then save the results to the
'sickness_days' table.
In writing the above I can see that my question may have become too long to
get responses from this forum! Obviously I don't expect anyone to give me
all the answers - I'm just looking for some advice about which way to go
with this, as I think I'm in danger of getting it horribly wrong! Any
general pointers would be very much appreciated.
Many thanks
Leslie Isaacs
I have to create a facility within an existing 'Employee' database to manage
employee sickness absence. The data to be input will be the employee's name,
and the start and end dates of any sickness absence periods. These dates,
together with some other 'standing' data held in the existing 'employee'
table, will be used to calculate certain values for each day during the
sickness absence period: essentially, each day during the sickness absence
period will have to be annotated as being of one of 3 'types' (denoted as
"W", "S" or "N"). In addition, each day during the sickness absence period
will be assigned by the user to a 'pay period' (there is an existing table
of 'pay periods'). The rule that determines the 'type' will take account of
the length of the absence period, the proximity of other absence periods for
that employee, and certain other data held about the employee in the
'employee' table. Once calculated it is important that the 'type', and the
'pay period', that are assigned to each day during an absence period are
saved so that their values can be retrieved in the future. They cannot
always be re-calculated because some of the data used in the calculations
may change (primarily the data range of the sickness absence itself) - but
the results of the original calculation must be retained.
The main output needs to be a report showing, for a selected employee and
selected date range, every date between the selected date range, with each
date being displayed either with its 'sickness type' etc. if that date IS
during one of the employees absence periods, or with no 'type' etc. if it is
NOT during one of the employees absence periods. The actual layout of the
report must be to show the absence 'type' (in the case of absence dates), or
a blank field (in the case of other dates) for complete weeks horizontally -
e.g. with field names:
"Sun", "Mon", "Tue", "Wed", Thur", "Fri", "Sat", "Week ending"
the report might look like:
W W S S N N 26/07/08
S S N N N N N 02/8/08
07/8/08
14/08/08
N N N N S S N 21/8/08
The first row above would represent one blank representing 20/08/08, two
"W"s representing 21/08/08 and 22/08, two "S"s representing 23/08/08 and
24/08/08 and two "N"s representing 25/08/08 and 26/08/08. The third and
forth rows represent week with 7 'blanks' - i.e. these weeks contain no
dates that fall within any sickness absence period for the employee.
The assigned 'pay period' would not be included on this report.
My initial plan was to have a table 'sickness_periods', containing just the
employee name and the start and end dates of each sickness period, plus a
table 'sickness_days', containing the results of the calculations. Now I am
wondering whether I shouldn't bother with the first 'sickness_periods'
table, and instead just enter the data range into two unbound fields on the
data entry form, do the calculations and then save the results to the
'sickness_days' table.
In writing the above I can see that my question may have become too long to
get responses from this forum! Obviously I don't expect anyone to give me
all the answers - I'm just looking for some advice about which way to go
with this, as I think I'm in danger of getting it horribly wrong! Any
general pointers would be very much appreciated.
Many thanks
Leslie Isaacs