Employee Schedule

[Guest]

I have looked at Duane's reports and many others, but know one has anything
like this. If it has anything to do with crosstab queries I will need help
setting it up (great detail is appreciated). I would like to make an employee
schedule as follows:

EmployeeName DateSun DateMon ... DateSat
John Smith 11:00 9:00 18:00
Host Bus Bus
15:00
Bus
Sue Johnson 9:00 13:00 9:00

The dates at the top are from a table with dates for the next 40 yrs. (I
don't know if that is the best thing to do, but you would only have to have
the dates list once and not in multiple tables.) Each employee can work more
than one time per day and they work different positions, so I need the
position listed under their start time. I know how to group them by empoyee
name and date, and then have the posistion and start time in the details
section. My problem is I don't know how show the next six dates in columns
next to the first date (to be displayed as shown above.)

tblDates
DateID (PK)
RecordDate

tblSchedule
ScheduleID (PK)
EmployeeID (FK)
DateID(FK)
PositionID(FK)
StartTime

tblEmployee
EmployeeID(PK)
FirstName
...

tblPosition
PositionID(PK)
PositionName

Thanks

 

[Duane Hookom]

You should be able to use my calendar report. You would group by both WeekOf
and Employee. Rather than 5 subreports, you would need 7. These subreports
would be linked by the Employee and the date.

 

[Guest]

Are you refering to "Weekly Schedule by Doctor"? I am not that good with
advance reports and need a more detailed direction. If it is the forsaid
report, I didn't know there was 5 subreports. What would the subreports
display?

 

[Duane Hookom]

I was referring to the "Calendar Style Schedule". It more closely matches
your described format.

 

[Guest]

I'm sorry. I see what your talking about. I will work on that. Thanks!

 

[Guest]

Thanks, I am getting close.

Okay, I have the main report setup up and diaplaying the seven day week.
However, I can't seem to get the subreport to connect the main report because
I have a Dates table that is connected to the Schedule table. However, the
link doesn't recognize RecordDate from the query (a query used to link the
tqo tables). I could link them on the DateID which is what connects the to
above mentioned table together, but I don't know what to do. Could you give
me a little more help?

James

 

[Duane Hookom]

I am not sure why you even created a DateID field when the date field value
will always be unique.

You can add the actual date value to the record source of both the main
report and the subreport. Then you can ignore the DateID.