One table or two?

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

On Wed, 30 Jul 2008 12:11:01 +0100, "Leslie Isaacs"
wrote:

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 think you need a table like this:

CREATE TABLE Absenses (
employee_id INTEGER NOT NULL
REFERENCES Employees (employee_id),
absence_date DATETIME NOT NULL,
absence_type CHAR(1) NOT NULL,
CHECK (absence_type IN ("W","S","N")),
PRIMARY KEY (employee_id, absence_date));

Then using a calendar table you may be able to come up with a crosstab query
that does what you want.

I am only a hobbiest and only answered because I do not see any other responses.

If your requirements are to report on each day for the occurance, then you
need a record for each day. To tie the records together in to one occurance,
you could use a field that defines the occurance.
--
Dave Hargis, Microsoft Access MVP


"Leslie Isaacs" wrote:

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

On Wed, 30 Jul 2008 10:20:34 -0500, Michael Gramelspacher
wrote:

I think you need a table like this:

CREATE TABLE Absenses (
employee_id INTEGER NOT NULL
REFERENCES Employees (employee_id),
absence_date DATETIME NOT NULL,
absence_type CHAR(1) NOT NULL,
CHECK (absence_type IN ("W","S","N")),
PRIMARY KEY (employee_id, absence_date));

Then using a calendar table you may be able to come up with a crosstab query
that does what you want.

Based on the what I wrote above, this is the best result I can come up with
using a crosstab query.

EmployeeId First Last Sun Mon Tue Wed Thu Fri Sat
1 7/27/2008 8/2/2008 N N S S N N N
2 7/27/2008 8/2/2008 N N N N N N N

How about a form that looks like an analog calendar where you could choose
any year and month then choose an employee and display what days that month
the employee was absent and for each absence show the absence type? You
would also be able to print a look-alike calendar report.

Steve



"Leslie Isaacs" wrote in message
...
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

Hello Steve

Many thanks for your suggestion.

By "analogue calendar" do you mean one that actually looks like a calendar -
like the calendar control? If so, creating a form that looks like that
sounds like a great idea ... but I can't think how to create such a thing!

Any help would certainly be appreciated.

Thaks again
Les


"Steve" wrote in message
m...
How about a form that looks like an analog calendar where you could choose
any year and month then choose an employee and display what days that
month the employee was absent and for each absence show the absence type?
You would also be able to print a look-alike calendar report.

Steve



"Leslie Isaacs" wrote in message
...
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

Careful Leslie, Steve is a known troll in these newsgroups. His sole purpose
is to get users to ask him to do work. Microsoft provides these newsgroups
for FREE peer to peer support. This is one of steve's tactics to get around
that issue. There are many here who will help you for no more than a thank
you.

He has been trolling these newsgroups for years and the fact that he has to
continue to do it indicates that he does not get much repeat business, which
speaks to the quality of his work.

John... Visio MVP

"Leslie Isaacs" wrote in message
...
Hello Steve

Many thanks for your suggestion.

By "analogue calendar" do you mean one that actually looks like a
calendar - like the calendar control? If so, creating a form that looks
like that sounds like a great idea ... but I can't think how to create
such a thing!

Any help would certainly be appreciated.

Thaks again
Les

Hello Les,

The form (and report) looks like a page you would tear off a calendar on the
wall. You could choose
any year and month then choose an employee and display what days that month
the employee was absent and for each absence show the absence type. I could
implement the calendar for you for a very reasonable fee.

Steve






"Leslie Isaacs" wrote in message
...
Hello Steve

Many thanks for your suggestion.

By "analogue calendar" do you mean one that actually looks like a
calendar - like the calendar control? If so, creating a form that looks
like that sounds like a great idea ... but I can't think how to create
such a thing!

Any help would certainly be appreciated.

Thaks again
Les


"Steve" wrote in message
m...
How about a form that looks like an analog calendar where you could
choose any year and month then choose an employee and display what days
that month the employee was absent and for each absence show the absence
type? You would also be able to print a look-alike calendar report.

Steve



"Leslie Isaacs" wrote in message
...
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

"Leslie Isaacs" wrote in message
...
Hello Steve

Many thanks for your suggestion.

By "analogue calendar" do you mean one that actually looks like a
calendar - like the calendar control? If so, creating a form that looks
like that sounds like a great idea ... but I can't think how to create
such a thing!

Any help would certainly be appreciated.

Thaks again
Les


Tony Toews, one of the Access MVPs who loves to help out has a webpage on
the subject.
http://www.granite.ab.ca/access/calendars.htm

John... Visio MVP

"Steve" wrote in message
...

I could implement the calendar for you for a very reasonable fee.

Steve

True to form and as predicted, you have crawled out from under your rock and
are pestering the newsgroup users for work.

These newsgroups are provided by Microsoft for FREE peer to peer support,
not as a venue for you to offer your questionable services.

Roberta must be really impressed with your sleazy business practices.

John... Visio MVP