In many of your reports, you will probably design the Detail section to display a single line of information from the underlying record source. As you learned earlier in this chapter, it’s fairly easy to link several tables to get lots of detail across several one-to-many relationships in your database. You also saw how to use the Hide Duplicates property to display a hierarchy across several rows of detail.
However, as with forms and subforms, which you learned about in Chapter 13, you can embed subreports (or subforms) in the Detail section of your report to display multiple detail lines from a table or query that has a many relationship to the one current line printed in the Detail section. You must use this technique when you want to display information from more than one many relationship on a single page. In the Conrad Systems Contacts database, for example, if you want to provide details about contact events and products owned by a contact, you must use subreports. You could create a very complex query that joins all the information, but you’d get one row for each unique combination of contact event and product. If a contact has 100 events and owns six products, each of the six product rows is matched with each of the 100 contact event rows. You’ll get 600 rows for that contact in a query that joins the tblContacts, tblContactEvents, and tblContactProducts tables-each product record appears 100 times, and each contact event record appears six times.
Subreports present a unique challenge. Unlike a subform where you can scroll through all available related rows in one window, a subreport has no scroll bar. The subreport expands to list all the related rows. If the rows won’t fit on one page, it can be difficult to repeat the header information at the top of the subsequent pages. Although you can define a report header in a report that you use as a subreport, that header prints only once at the top of the subreport space on the page where the subreport starts.
To understand how this works, let’s examine two approaches to listing department information in a report with related employee information in a subreport. In the HousingDataCopy2.accdb sample database, open rptDepartmentsWSubBad in Design view, as shown in Figure 16–29. When you drop a report onto the design of another report to create a subreport, Access sizes the subreport control to the height of one line from the report inside the control. The figure shows the subreport control expanded so that you can see the subreport inside it. We selected the control and dragged down the bottom edge, but you might find it easier to change the Height property in the property sheet because the bottom sizing box is difficult to grab with your mouse pointer.
Figure 16–29: This report displays departments with related employees in a subreport.
The outer report, Departments, uses a query based on the tblDepartments and tblEmployees tables to provide information about each department and the department’s manager. The report inside the subreport control, Employees, has another query on the tblEmployees table. The report looks simple enough-a heading for each department row and a heading inside the subreport to provide column headings for the employee information. You can also see that a subreport works just like a subform-you define the Link Master Fields and Link Child Fields properties of the subreport control to link the information from the two reports.
Now switch to Print Preview, and go to the fourth and fifth pages of the report, as shown in Figures 16–30 and 16–31, to see what really happens when a department has more employees than will fit on one page.
Figure 16–30: The top of the fourth page of the departments and employees report displays the header information.
Figure 16–31: The top of the fifth page of the departments and employees report has missing headers.
As you can see, the fifth page has nothing more than the page header to help identify the information being printed. The detail department information printed once on the fourth page, as did the report header from the subreport. When the subreport overflowed onto a second page, the column heading information from the report header defined for the subreport didn’t print again.
To see how to solve this problem, open rptDepartmentsWSub in Design view, as shown in Figure 16–32. Again, the figure shows the subreport control expanded so that you can see the subreport inside it.
Figure 16–32: This design of a report and subreport handles the page overflow problem.
Can you figure out the difference? The secret is the outer report has a group defined on department, even though there is only one detail row per department. All the department information and the headers for the columns in the subreport appear in this group header. Remember that you set the Repeat Section property of a group header to Yes to force it to appear again at the top of a page if the information in the Detail section overflows the page. The Detail section contains only the subreport, and the subreport has no headers. If you switch to Print Preview and go to the fifth page in this report, as shown in Figure 16–33, you can see that the appropriate headers appear again when the 04 Product Development department overflows onto another page.
Figure 16–33: The top of the fifth page of the departments and employees report in this sample has headers correctly repeated.
The manager of Housing Administration has just asked you to produce a report that summarizes for each facility the revenue by month. You know the manager is someone who likes to see a visual representation as well as the data, so you need to design a report that will ultimately display a revenue chart as well. You’ll learn how to add the PivotChart in the last section of this chapter.
If all you needed to do was display total revenue by facility and by month, you could build one totals query that joins the tblFacilities table with the tblReservations table and group by facility. However, the need to add the chart means you’ll need a subreport to calculate the monthly totals so that you can display the chart that graphically shows all the month values immediately below the numerical data. If you try to add a chart to the Detail area of a report that displays totals by month, the chart will show one graph point for the current month, not all months.
In the previous examples, you have been using a complex query to calculate revenue by day-an accounts receivable perspective. But guests in a hotel usually don’t pay for their stay until the day they check out. So, to calculate actual revenue received for a month, you should use the check-out date and the total amount owed.
Start a new query on the tblReservations table. In the query design grid, include the FacilityID (you’ll need this field to provide the link between the subreport and the main report), CheckOutDate, and TotalCharge fields. You could turn this into a totals query to sum the total charge by month, but it’s just as easy to do that in the report. Your query should look like Figure 16–34. You can find this query saved in the sample database as qryXmplFacilityRevenue.
Figure 16–34: This query for the subreport calculates revenue by facility and month.
The report you need for the subreport is very simple. Click the Report Design button in the Reports group on the Create tab to start designing your report. If you want your report to look like the Facility Occupancy report earlier in this chapter, click the AutoFormat button in the AutoFormat group on the Arrange tab, and select the Access 2007 style from the gallery of options. Remember from the discussion in the previous section that you’ll see what’s in the report header and report footer of a report that you use as a subreport, but Access never displays the page header or page footer. Click the Page Header/Footer button in the Show/Hide group on the Arrange tab. Now click the Report Header/Footer button in the same group. You now need to bind this report to the qryXmplFacilityRevenue query (or the query you created earlier). Open the property sheet and set Record Source to qryXmplFacilityRevenue.
Open the Group, Sort, And Total pane by clicking the Group & Sort button in the Grouping & Totals group on the Design tab. Click the Add A Group button to start a new grouping specification. Select the CheckOutDate field in the Select box, click the More button to expand the option list, set the group interval to By Month, and then close the pane. Because you want the report to calculate and display totals by month only, close up the Detail section to zero height. Reduce the report design area to about 5.5 inches. Close up the Report Header section to zero height because you’ll add the column labels to the outer report.
Draw a line across the top of the CheckOutDate Header section starting near the left edge and extending to about 5.1 inches wide. In the Property Sheet window, set the Border Style property to Solid and the Border Width property to 1 PT. Underneath this line about 1.5 inches from the left, drag and drop the CheckOutDate field from the Field List window, and delete the attached label. In the Property Sheet window, set the Format property to mmmm yyyy to display the month name and four-digit year, and change the Width property to 1.5 inches. Drag and drop the TotalCharge field from the field list into the CheckOutDate Header section about 3.75 inches from the left, and delete the attached label. In the Property Sheet window, expand the width to 1.2 inches, change the Name property of the control to TotalChargeSum, and change the Control Source to =Sum([TotalCharge]). (Remember, you must change the name of the control to avoid a circular reference in the expression!) Line up the two text boxes in the CheckOutDate Header section horizontally.
Finally, click the Text Box button in the Controls group on the Design tab, and add a text box to the Report Footer section lined up under the TotalChargeSum text box. Set its Control Source property to =Sum([TotalCharge]), change the font to Bold, and set the Format property to Currency. Change the caption of the attached label to Grand Total, change the font to Bold, and position it near the left edge of the Report Footer section. Your report should look something like Figure 16–35. You can find this report saved as rsubXmplFacilityRevenueByMonth in the sample database.
Figure 16–35: This is your subreport to summarize revenue by month.
You can find the query you need for the outer report saved as qryRptFacilities in the sample database. This query includes the FacilityID, FacilityName, and FacilityAddress fields. It also includes an expression (named FacilityCSPP) that concatenates the FacilityCity, FacilityStateOrProvince, and FaciltyPostalCode fields so that they display nicely on one line in the report.
Click the Report Design button in the Reports group on the Create tab to start your report design. Click the AutoFormat button in the AutoFormat group on the Arrange tab, and select the Access 2007 style from the gallery of options. Expand the report to 6.5 inches wide. Open the Property Sheet window, and enter qryRptFacilities in the Record Source property to bind the report to the saved query. Expand the Page Header section to 0.5 inch, add a label control to the Page Header section, and type Facility Revenue in the label. Change Font Size to 20, size the label to fit, bold the text, and position it in the upper-left corner of the Page Header section.
Open the Group, Sort, And Total pane by clicking the Group & Sort button in the Grouping & Totals group on the Design tab. Click the Add A Group button to start a new grouping specification. Select the FacilityName field in the Select box, click the More button to expand the option list, set the last column to Keep Header And First Record Together On One Page, and then close the pane. Expand the FacilityName Header section to about 1.25 inches high to give yourself some room to work. Select the FacilityName Header section, and in the Property Sheet window, set the Force New Page property to Before Section.
Drag and drop all four fields from the Field List window onto the FacilityName Header section of the report one at a time, as shown in Figure 16–36. Delete the attached label for the FacilityCSPP control, expand the width of the FacilityName text box to 1.6 inches, and expand the width of the FacilityAddress and FacilityCSPP text boxes to 3.2 inches. Bold the text of the three labels in the FacilityName Header section. Click the Label button in the Controls group on the Design tab, and place a label control under the FacilityCSPP text box control about 2.5 inches in from the left. Type Month in the label, and press Enter. (You can ignore the smart tag warning about an unattached label.) Add a second label control about 4.5 inches in from the left, type Revenue in the label, and press Enter. Line up the two labels horizontally, and bold their text. Click the Line Control button in the Controls group on the Design tab, and add a line at the top of the FacilityHeader section. In the Property Sheet window for this line, set the Border Style property to Solid, set the Border Width property to 1 PT, and set the Width property to 4.5 inches.
Expand the Navigation Pane if it is collapsed, click at the top of the Navigation Pane, click Object Type under Navigate To Category, and then click Reports under Filter By Group to display a list of reports in the sample database. Drag and drop the report you created in the previous section (or the rsubXmplFacilityRevenueByMonth report) from the Navigation Pane onto the Detail section of the report into the upper-right corner about 0.25 inch in from the left edge. In the Property Sheet window, set the Link Child Fields and Link Master Fields properties to FacilityID. Delete the label that Access attached to the subreport control, and then reduce the height of the Detail section to about 0.36 inch.
As a finishing touch, you can add a date text box control and a page number text box control to the Page Footer section as you learned to do earlier in this chapter. Also, you should change all the margins for the report to 1 inch using the Page Setup dialog box and enter a caption of Facility Revenue for the report in the Property Sheet window. Your report should look something like Figure 16–36. You can find this report saved as rptXmplFacilityRevenue in the sample database.
Figure 16–36: The design of your report now includes a subreport.
Switch to Print Preview to see the result as shown in Figure 16–37. If the Month and Revenue labels aren’t correctly positioned over the columns in the subreport, switch to Layout view to easily make the adjustment.
Figure 16–37: Your report now displays facility information with monthly revenue in a subreport.