Hello Marshall
Thanks for your reply. I have used Allen's suggestion, which looks very much
like yours ... and this gets me to the next step: as you guessed, this is
part of a bigger problem - the next part of which I am now struggling with!!
I need the report to show one row for each Saturday date. In each row there
must be 8 columns: one for the Saturday date, and one for a calculated value
corresponding to each of the 7 dates in that week (including the Saturday).
I did try producing the report by basing it on a table with a row for ALL
the dates within the specified range (as opposed to just the Saturday
dates), and specifying 8 columns in page>setup, but that didn't work for
reasons that are probably obvious to you!
The full task is as described in my post today on the Table Design newsgroup
(headed "One table or two"), which I have copied below. I was (and still
am!) concerned that that post seems too long for anyone to get into. In the
light of your and Allen's replies to my post in this Reports newsgroup, I'm
now confused about whether I need a table with a row for each date within
the range, or just a row for each Saturday within the range. If I just go
for the Saturday dates I think the required layout of the report will be
much easier to achieve: but I would then need 7 further columns in the
table, labelled "Sun", "Mon", "Tue", "Wed", Thur", "Fri" and "Sat", to
store the calculated values corresponding to each day.
I hope this makes sense, and I really would be very grateful if you could
let me know your thoughts.
Many thanks once again
Les
My post in the Table Design newsgroup was:
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.
