Printing a Report Based off a Sub Field (1 Viewer)

Hi - I am trying to create a report for our student body that lists out student schedules by student, then groups based off of first period class. The purpose of this is the first day of school we want to hand out a paper copy of the student schedules, and we want the printouts organized by room so we can quickly distribute.

What I have currently is a very simple table:

StudentID, Student Last Name, Student First Name, Class, Teacher Last Name, Teacher First Name, Period, Room

I then created a concatenated field of Room and Period, so it would be something like 2-Room100, meaning period 2 room 100.

I can create the report very easily to print out each student schedule by grouping by student ID. But then I want the order the student schedule groupings to appear to be based off of the first period, or this concatenated field I created. This is where I am stuck. I can seem to only group based off of student ID or the concatenated field...

Any ideas on how I can get the groups of schedules to be ordered by 1st period? I have been working on this for a couple of hours and I can't figure it out...

Suggest you post a stripped down version of your database with some realistic but modified data for someone to have a look at it

Thanks ridders. That is what I have done.

Attached is a sample with 4 students. This form was created in 60 seconds, so it is very ugly - just trying to convey the idea.

The first and 4th student both have 1st period BC Calculus. What I want to have the report do is group the students together so they can have their schedules, but then then next layer of sorting is I want the students sorted by their first period class so that when we send 3000 paper schedules to the printer to hand out the first day, they are already arranged by 1st period room so that we can easily group and deliver to those rooms.

Not sure where to go after spending multiple hours on this yesterday with different grouping ideas...

Answering on a tablet so can't check this until later
Will look this evening UK time unless someone else does so first

As a suggestion try adding a concatenated field PeriodCourseTitle: Period & " " & [Course Title] to your report record source.
Try grouping / sorting by that as well.

Is this really your data format or did you just put this together because it seemed to be what you needed to create this report?

The problem is that to make this report you need a main report by first period room and a subreport to show the schedule for each individual. Access is difficult to control when there are two break levels (room) and (student) so I just created a concatenated field so that the break would happen correctly for the combination of the two fields.

If your data really is as you presented it, it needs to be normalized. The one table should be at least five tables to avoid the horrendous duplication you are headed for.
Students
Semester
Classes
Teachers
Schedule

I changed from tab view to window format just because I find it hard to design using tab view since I can't see more than one object at a time. Just change it back if you prefer the tab view.

Pat Hartman said:

Is this really your data format or did you just put this together because it seemed to be what you needed to create this report?

The problem is that to make this report you need a main report by first period room and a subreport to show the schedule for each individual. Access is difficult to control when there are two break levels (room) and (student) so I just created a concatenated field so that the break would happen correctly for the combination of the two fields.

If your data really is as you presented it, it needs to be normalized. The one table should be at least five tables to avoid the horrendous duplication you are headed for.
Students
Semester
Classes
Teachers
Schedule

I changed from tab view to window format just because I find it hard to design using tab view since I can't see more than one object at a time. Just change it back if you prefer the tab view.

Click to expand...

Pat - thanks for this! Very helpful! One other question... using the exact same set of data, how would I create a report that would have the row headings are periods, the columns are the students, then the intersection of the rows and columns would have what class a student is in for a given period? We are trying to come up with a report where we can quickly see where a student is in a large grid view.

Easiest? Correct the data first so that you have a "Period" table you can do a cartesian join to.

With what you have? you will want to build one query for each period then join the queries together to get your record source.

Query0 would have your student information for the semester
Query1 would have the student ID, class, and period but would filter to period = 1.
Query2 would have the student ID, class, and period but would filter to period = 2.
Query3 would have the student ID, class, and period but would filter to period = 3.
.
.
.
.

This way you would query your query0, join it to query1 by StudentID, join to query2 by StudentID, ect... to replicate a cartesian join.

What you should have are
Table for students.
Table for Classes
Table for Teachers
Table for Rooms
Table for Semester
OPTIONAL Table for periods.
You would then have the following tables that link other tables
Table Session - Joins a class to semester (and optionally a room and period)
Table SessionInstructors - Joins Sessions to instructors
Table SessionRoster - Joins Students to a session

You would then be able to pull a report based on sessions for a given period and generate schedules for each student as a subreport.

Thanks - I didn't think of that. Great idea - I am going to try that! I tried creating a different sub-report for each period, and with 8 different periods it was causing Access to hang. Exponentially long to load. When I had 3 or 4 periods it worked fine, but by the end it was too resource intensive to simulate what I wanted.