|

Access: Using a Subreport Multiple Times

By Crystal Long
November 1, 2019
0 comments

When you’re building a report, it often makes sense to use a main report to show some fields and then link one or more subreport to display other fields, rather than putting all fields on the main report. The added benefit is that a subreport can be used more than once.

 

Previously, we had been working on a sample database used by one schoolteacher to cut down on paperwork and track students, classes, and grades. Then other teachers got interested, and its use expanded. Now let’s suppose the school wants to use it to track overall student performance.

 

 

Figure 1 shows part of a student’s transcript. The transcript is a main report based on student data and containing multiple subreports. It uses one subreport four times to display the student’s classes and grades for grade levels nine through 12. We can reuse the same subreport to display the different data because the type of information presented for each grade level is the same.

 

ACADEMIC RECORD

 

The Academic Record part of the transcript uses a subreport named “rStudentTranscriptLev_sub” multiple times. The query for each subreport is based on five tables with this SQL:

 

SELECT StudentClass.StudentID

, Classez.YearID

, StudentClass.ClassID

, StudentYear.Lev

, Yearz.SchoolYr

, Coursez.CourseName

, StudentClass.Grade

, StudentClass.Credits

, StudentClass.Points

FROM Yearz

INNER JOIN (Coursez

INNER JOIN (Classez

INNER JOIN (StudentClass

INNER JOIN StudentYear

ON StudentClass.StudentID = StudentYear.StudentID)

ON (Classez.ClassID = StudentClass.ClassID)

AND (Classez.YearID = StudentYear.YearID))

ON Coursez.CourseID = Classez.CourseID)

ON Yearz.YearID = StudentYear.YearID;

 

The Coursez table has records of all the courses taught, such as “Algebra 1.” The Classez table stores the specific occurrences of a course and includes the school year and term for each occurrence.

 

StudentClass is a cross-reference table between students and classes. It stores the credits for the class and contains calculated fields for letter grade and points. Normally, storing calculations isn’t a good idea, but an exception can be made when values become a part of a permanent record.

 

The Yearz table includes a text field for school year, such as “2019-2020.” StudentYear is a cross-reference table between students and school year and also has the student grade level (Lev), which is 9, 10, 11, or 12.

 

 

Figure 2 shows the Design view of the Academic Record portion of the main transcript report. It contains four copies of the rStudentTranscriptLev_sub subreport, one for each grade level. When the same subreport is used multiple times, the design will only appear for the first occurrence. Copies are displayed as white boxes that simply show the object type (Report) and source object name.

 

Controls that aren’t visible in the report appear with a black background color to make them stand out in Design view. StudentID isn’t displayed, but it’s in a control so it can be used for linking. You can also see four unbound controls that are used in linking each of the subreports. The control sources are =9, =10, =11, and =12, one for each grade level, and are called Lev9, Lev10, Lev11, and Lev12, respectively.

 

The rStudentTranscriptLev_sub subreports are linked to the main report through the combination of StudentID and Lev. StudentID is in the record source for the main report, but Lev isn’t, which is why unbound controls are used. The property sheet shows that the ninth grade subreport has StudentID;Lev9 in the LinkMasterFields property and StudentID;Lev in LinkChildFields, which is the same for each subreport. The 10th grade subreport contains StudentID;Lev10 in the LinkMasterFields, and so on.

 

The CanGrow and CanShrink properties for each subreport are set to Yes so they will grow as big as they need to be or shrink if there isn’t much to show. Notice that 10th grade has more classes than ninth grade, but they line up with each other. The 11th and 12th grade subreports also align.

 

SUMMARY PORTION

 

The Summary part of the transcript contains a subreport called “rStudentTranscriptSummary_sub,” which is based on a query with this SQL:

 

SELECT q.Lev

, Sum(q.Credits) AS LevCredits

, Sum(q.Points) AS LevPoints

, CCur([LevPoints]/[LevCredits]) AS GPA

, Round([C_Points]/[C_Credits],2) AS C_GPA

, q.StudentID

, DSum(“Credits”,”qStudentClassLev”,”StudentID=”

& [StudentID]

& ” AND Lev<= ” & [Lev]) AS C_Credits

, DSum(“Points”,”qStudentClassLev”,”StudentID=”

& [StudentID]

& ” AND Lev<= ” & [Lev]) AS C_Points

FROM qStudentTranscriptLev AS q

GROUP BY q.Lev

, q.StudentID;

 

C_Points (cumulative points) and C_Credits (cumulative credits) use the DSum function to add points or credits for the respective grade level as well as all previous grade levels. The qStudentClassLev query has StudentID, Lev, Credits, and Points. It gets this information from the StudentClass and StudentYear tables and joins in the Classez table to link them.

 

The report footer section has three hidden controls to sum points and credits and to calculate the grade point average (GPA). Total points, total credits, and final GPA are then displayed on the main report. The graduation date comes from the Studentz table.

 

Download the sample database for this story: SF1911_SchoolTranscript.

 

SF SAYS

 

You can use an unbound control name in LinkMasterFields. This is handy when you have a subreport that’s used multiple times.

 

Crystal Long teaches and develops with Microsoft Access, Excel, and Office and specializes in remote training. She connects and helps as your project is built. Visit www.MSAccessGurus.com for information. Share your perspective! Add your comments about the article below.
0 No Comments

You may also like