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

 

[Jeff Conrad]

Hi Duane,

You are the man Duane!!

Your suggestions were SPOT ON!!

I can't begin to list all the things I had tried without
success, but implementing your quick suggestion was just
the direction I needed. Without worrying anything about
formatting, sorting, etc. the raw data that came out the
FIRST time I ran the report I could tell was exactly what
I was needing. Just changing the record source as
suggested and making a minor adjustment to the sub report
was perfect.

Since then I have been trying to tweak everything to make
it "look" just perfect with everything I needed.

My first stumbling block was changing the Print Line code
you have. I had to have 7 sub reports instead of your 5
and I had to have room for the employee name on the left
side. Naturally the line printing was way off. I studied
your code over and over and tried countless things, but I
could not adjust the first line at all. I studied the Help
file on the Line method and everything seemed to make
sense, but still no positive results. After many tiresome
hours my "What would Duane do?" message FINALLY came
through. It said, "Duane would remember that measurements
are in twips not inches!!" Ahhh haaaaa. Bingo, now the
lines are just where I want them.

The second stumbling block came with Sorting. I needed to
have the report sorted by Job Code and then by last name.
Well of course this meant having to change the record
source of the main report. At that point it had just the
EmployeeID and WeekOf as you suggested. Adding extra
fields to the query suddenly screwed everything up! My 30
employee sample report now only showed 24! Not good. I'm
sure it was a problem with Inner Joins, Outer Joins, Left
Joins, Out-Of-Bounds Joins, etc. Finally got it just right
though.

I experimented with the "XXXX"s across the blank sub
reports, but it was a little over-powering so I decided on
having just a few dash (- - -) marks. It looks much better
this way I believe.

Anyway, the report looks totally SWEET!! It is EXACTLY
what I was looking for and even has color for the
different positions. The managers are really going to like
this.

Thanks yet again for the help and samples to create some
awesome looking reports. I would be happy to send you this
sample report if you'd like to see it. Just let me know!

Jeff Conrad
Bend, Oregon

 

[Duane Hookom]

I would like to see a Snapshot or PDF version if you can make one up for me.
Glad to hear you had success.

 

[Jeff Conrad]

Hi Duane,

OK, one 2-page snapshot report coming up.

Now I just need to fix an appointment report I have to
adjust.....but that's for another day and post.

Jeff Conrad
Bend, Oregon

 

[Jeff Conrad]

I think I sent it to your work e-mail address.
You may not be able to get it until you go into work
again, unless you can check it from home.

Jeff Conrad
Bend, Oregon

 

[Duane Hookom]

Got the report and it looks fantastic!