J
Jeff Conrad
Hi,
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
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