Creating a report like a crosstab report

[MLEBL]

I have information in my scheduling table that I need to report out in a crosstab format with the staff members as the rows and the dates as the columns. I've created a crosstab report query that gives me the information in the exact format I need, but the problem is that since the columns are based on a dynamic value that changes (schedule date), I can't manipulate the information into the format I need. The request is to break down the schedule into specific segments based on the staff member's role in the department by location, so I'd need a report for all RN's scheduled at Station A, all LPN's scheduled at Station A . . . all within one document for a selected amount of time (one day, two days, one week, two weeks, four weeks, six weeks).

Basically, I'd like to take data in the format:
Mouse, Micky 12/23/2012 1900-0700
Mouse, Minnie 12/23/2012 0700-1500
Mouse, Micky 12/24/2012 1900-0700
Mouse, Minnie 12/24/2012 0700-1500
Mouse, Micky 12/25/2012 1900-0700
Mouse, Minnie 12/25/2012 0700-1500

and convert it to:
RN Schedule
12/23/2012 12/24/2012 12/25/2012
Mouse, Micky 1900-0700 1900-0700 1900-0700

LPN Schedule
12/23/2012 12/24/2012 12/25/2012
Mouse, Minnie 0700-1500 0700-1500 0700-1500

(since Micky is an RN identified in the staff table and Minnie is an LPN)

Here's the format of the relevent tables:

Table - Schedule
CreateDateTime Date/Time
ScheduleDate Primary Key Date/Time
ScheduleStaffMember Primary Key Number (Lookup to Staff Table)
ScheduleStaffShift Primary Key Number (Lookup to ListOfShifts table)
ScheduleStaffRole Number (Lookup to ListOfPosition table)
ScheduleStatus Text
ScheduleStaffShiftStart Date/Time
ScheduleStaffShiftEnd Date/Time

Table - Staff
ID Primary Key AutoNumber
StaffName Text
Role Number (lookup to ListOfStaffRole table)
PreferredArea Number (lookup to ListOfAreas table)
PrimaryContactNumber Text
FTEStatus Number (lookup to ListOfFTE table)
PreferredShift Number (lookup to ListOfPreferredShift table)
PreferredHours Number (lookup to ListOfPreferredHours table)
HireDate DateTime
DeployDate DateTime
Agency Yes/No
Traveler Yes/No
ActiveStatus Yes/No
DistantEmployee Yes/No
Wave Number (lookup to Wave table)

Table - ListOfShifts
ID Primary Key AutoNumber
ShiftDescription Text
StartTime Text
EndTime Text
Duration Number

Table - ListOfPosition
ID Primary Key AutoNumber
Position Text

[zmbd]

We just had a question like this not very long ago... let me see if I can re-locate the thread.

[zmbd]

This isn't the thread I was thinking of; however, I have it in a list I use so this should get you started. http://bytes.com/topic/access/answer...text-box-value

Of relavance to your question take a look at post #2, #18, and #24.

[MLEBL]

I think this is good if I wanted to create graphs, but the system will not allow a crosstab query to be the basis for a subreport on a report or form. Any other direction?

[Rabbit]

While the content of that thread is for a graph, the solution of dynamically updating the fields to represent the differing columns applies to your situation.

[zmbd]

The link to the example database in Roger's library deals specifically with your issue.

[MLEBL]

I've realized that there's an additional component to the report, the role the staff member is expected to fulfill. So I would need a crosstab report with rows for the staff member, columns for the dates, and values for the role and shift. Is there any way to create a crosstab query with two values?

[zmbd]

Crosstab query techniques

[MLEBL]

Got it! I was thinking of it as two separate values instead of combining the values into one field. Now I'm just working on getting the correct format on the report based on the indicated criteria (single staff member, schedule for a day, week, two weeks . . .). I'll review the information provided. Thanks for all of your assistance with this!