How do I control when the group header executes?

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

I'm printing a schedule with 23 potential time slots each day. On my
report, I need to have a space for each time slot - whether it is actually
filled with an employee or not. If there is no employee scheduled for a
particular time then the start time for the shift should print followed by a
blank space to the right of the time.

My report is grouped on date. I want the date to be at the top of the
column, with start times and employees directly below it. I've fiddled
arount with the OnFormat event for the group header and detail section
extensively trying to figure out how to predict when Access with execute the
group header, but I'm at a loss.

Access default behavior causes it to get to the end of a group (get to the
end of the records with the same date), then execute the OnFormat event for
the group header, going to the next date group. Sounds perfect, but I'm not
able to insert the missing shifts before the group header prints.

I haven't had any problems inserting the time and it's associated blank area
to the right when it is in the middle of the date group. For example:

Jan 1st
07 Miller
07 Barton
07
07
11 Turpen
etc.

I can't tell when the last record in the date group executes, so the result
is something like:

19 Murphy
23 Miller
Jan 2nd
23
23
07 Martin
07 Smith
etc.

As you can see, I can add the shifts (with no associated data in the
recordsed) after the group header executes. I've thought of a couple ways I
can control the output, but need your help with them. If there is a way to
find out how many records are ACTUALLY in a group, I can use a counter and
Case statements to insert the 23 needed slots before allowing the group
header for the next day to execute. OR, if there is a way to tell when the
group header will execute NEXT, I could similarly delay the event till I run
through the appropriate number of time slots, then let it continue. Any
other ideas/suggestions?? I'm pretty much a novice - so feel free to spell
it out for me in simple terms. Thank you, all.

Michael
 
Let me try to figure out first how you intend to use this.
I think you intend to have like 5 or 6 time slots for each
hour. Are these sequentially or are you like limited to
having let us say 5 people for 7 AM than 5 at 8 AM etc.
throughout the day?
If this is the case than I would create a "timeslot" table
with just the available hours ( 5 records for 7am 5 for
8am etc.) and include a timeID let us say labeled A
through V (I think that is 23).
When slotting persons for the time slots, I would link
these "appointments" to a date and to the "timeslot"
tables.
Now when I create an input screen I have 23 slots
available and the report would list also all 23 slots with
or without a persons name.
Hope this helps.
Fons
 
I think that I have the tables that you are listing. Here's my structure:

tblShifts
ShiftID <PK>
ShiftName
ShiftStartTime

tblSchedule
ScheduleID <PK>
ShiftDate
PersonnelID <FK>
ShiftID <FK>
ShiftStartTime
ShiftDurationMinutes

tblPersonnel
PersonnelID <PK>
LastName
FirstName
*** etc. demographics ***

I've tried various join types and groupings, but still end up with the time
slots with no associated person being supressed. The only way I get all 23
shifts to display on the report is by having the tblShifts as the data
source for the report.

As for the layout of the shifts, there are eight 7am openings, one 11am, one
3pm, seven 7pm openings, one 11pm, and 5 'flex' shifts - could be any
position, and encompase a partial shift.

When I complete the schedule, there have to be blanks to give room for
employees to sign up for extra shifts on the paper copy.
 
Sorry for not getting back sooner. (but I have to make a
living also)
The only way I see to get a report listing all shifts is
to use the tblShifts as the source data however you can
include than the tblSchedule (linked to the tblShifts by
ShiftID "YOU MUST ADD THIS TO tblSchedule) and you may add
the tblPersonnel linked to tblSchedule by the PersonnellID.
The link between tblSchedule and tblShifts would be "all
from tblShifts and only matching from tblSchedule.

If you can not get this to work you may contact me at:
f o n s p o n s i o @ m s n . c o m
If you can than send me an mdb with some sample data I'll
gladly take a look at it.

Fons
 
Back
Top