Repost: Trouble with Employee Schedule Report

[Jeff Conrad]

Hi, thought I'd give this one more shot.

Using Access 97 here.

I'm having difficulty putting together a specific type of
report that will list a weekly schedule of employees. I've
made several other types of scheduling and appointment
reports using Duane Hookom's awesome calendar reports
sample database. I even made a real slick Daily Labor Plan
using his examples.

Basically I currently have three ways to print out the
weekly employee schedules to accomodate different
manager's "tastes" so to speak. One uses sub-reports, one
prints in a column format, and the other lists in a pretty
standard job code grouping. This last report I'm working
on is the last piece of the puzzle. My other ones, for the
most part, group the list by day and job code, but this
one needs to look like this:

010 Cook:
Monday Tuesday
Goldgar, Dirk 8:00 AM - 4:00 PM 10:00 AM - 3:30 PM
010 Cook 012 Baker

Hookom, Duane XXXXXXXX 2:00 PM - 10:00 PM
010 Cook

012 Baker:

Boer, Fred Monday Tuesday
8:00 AM - 1:00 PM 8:00 AM - 3:00 PM
012 Baker 012 Baker
5:00 PM - 8:00 PM
070 Cashier

Hope that comes out OK when posted.

I need to list each person's assigned shifts across like
one row. On the form that launches the report I will limit
the selection to a seven day time span. This layout most
closely resembles how managers write out the schedules by
hand so you can see why having this report would be a
great asset to the program.

I'm sure the key to this whole thing is to build some
slick cross-tab query and then use some sub-reports, but I
keep hitting brick walls. I've been trying to seek
inspiration by saying to myself over and over "What would
Duane do?", but I'm still stuck.

Here are the tables involved:

tblEmployees
EmployeeID (Autonumber) PK
LastName (Text)
FirstName (Text)
JobCodeID (Number) FK
.....etc...

tblJobCodes
JobCodeID (Autonumber) PK
JobCode (Text)
JobDescription (Text)
PositionColor (Number)

tblSchedule
ScheduleID (Autonumber) PK
ScheduleDate (Date/Time)
StartTime (Date/Time)
EndTime (Date/Time)
EmployeeID (Number) FK
JobCodeID (Number) FK

Relationships should be pretty easy to spot. The employees
are assigned one main job code, but they can work in other
areas as well. That is why JobCodeID is in the Schedule
table. (When filling out the schedule records the manager
can override the default Job Code for that employee and
fill a different one in.)

The form to launch the report is frmPrintWeeklySchedule.
The two text boxes on the form to gather the date range
are txtBeginningDate and txtEndingDate.

Here are the trouble spots for me:

1. There are times when an employee could be working a
split shift. They could, for example, be working in the
morning in one position and a completely different one in
the evening! How can I list BOTH shifts for that employee
on the same day? (Like Fred's example above) Each shift is
one record in the table.

2. How can I group these by the employee's MAIN job code,
but still have ALL their shifts listed across the report?
(Like Dirk's and Fred's examples above)

3. How would I handle the case where they are not working
at all that day? Like Duane's example above I just put a
bunch of "X"s in for illustration.

The more I think about it the more confused I get. :-(
Any ideas or inspiration are gladly welcome.

Thanks for your time,
Jeff Conrad
Bend, Oregon

 

[Duane Hookom]

I would use the standard calendar report from the demo. The report's record
source would have two fields: EmployeeID and WeekOf. Then, use the day text
box AND EmployeeID to link Master/Child.

Where you don't have data for an employee for a day, I would add 5-7 text
boxes with control sources like:
=IIf(srptDay1.Report.HasData,"","XXXXX")
You would need to use the actual subreport control names in these
expressions.

 

[Jeff Conrad]

Hi Duane,

Thanks so much for your time and input, I really
appreciate it.

Ok, I will work through your suggestions with due
diligence. If (more like when) I run into problems I will
post back with specifics on the trouble areas. That way it
will be easier to see.

Thanks again,
Jeff Conrad
Bend, Oregon

 

[Duane Hookom]

Got the report and it looks fantastic!