By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 2,392 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

Creating a report like a crosstab report

P: 25
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
Dec 27 '12 #1

Share this Question
Share on Google+
8 Replies


zmbd
Expert Mod 5K+
P: 5,331
We just had a question like this not very long ago... let me see if I can re-locate the thread.
Dec 27 '12 #2

zmbd
Expert Mod 5K+
P: 5,331
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.
Dec 27 '12 #3

P: 25
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?
Dec 28 '12 #4

Rabbit
Expert Mod 10K+
P: 12,341
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.
Dec 28 '12 #5

zmbd
Expert Mod 5K+
P: 5,331
The link to the example database in Roger's library deals specifically with your issue.
Dec 29 '12 #6

P: 25
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?
Dec 31 '12 #7

zmbd
Expert Mod 5K+
P: 5,331

P: 25
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!
Dec 31 '12 #9

Post your reply

Sign in to post your reply or Sign up for a free account.