You can use a wizard to quickly create a report that is bound to the information in one table or in several related tables. However, reports often include multiple sets of information that are related to the topic of the report but that are not necessarily related to each other. A report might, for example, include charts, spreadsheets, and other forms of information about several divisions or activities of a company. Or it might include information about production, marketing, sales, compensation, and the company’s 401(k) plan. All these topics are related to running the business, but they don’t all fit nicely into the structure of a single Access report.
One solution to this problem is to create separate reports, print them, and put them in one binder. An easier solution is to use subreports. A subreport is simply a report that you insert in another report. You create the subreport as you would any other report, and then use a wizard to insert it. You can also use a wizard to insert a subreport control in the main report, and then let the wizard guide you through the process of creating the subreport in the control. In either case, you end up with both the main report and the subreport listed as objects in the Reports pane of the database window.
Often you will use queries as the basis for reports that require summary calculations or statistics. But you can also enhance the usefulness of both regular reports and subreports by performing calculations in the reports themselves. By inserting unbound controls and then using the Expression Builder to create the expressions that tell Access what to calculate and how, you can make information readily available in one place instead of several.
Tip
If the correct relationships have been established, you can quickly add an existing report as a subreport by opening the main report in Design view and then dragging the second report from the Reports pane to the section of the main report where you want to insert it.
In this exercise, you will add a subreport to the main report created in the previous exercise. This subreport will display the total sales for each of the products in the category that is selected in the main report, as well as a calculated control for the total sales for the category.
USE the GardenCo database in the practice file folder for this topic. This practice file is located in the My DocumentsMicrosoft PressAccess 2003 SBSReportsSubreport folder and can also be accessed by clicking Start/All Programs/Microsoft Press/Access 2003 Step by Step.
OPEN the GardenCo database and acknowledge the safety warning, if necessary.
Open the Sales by Category report in Design view.
Click Subform/Subreport in the toolbox, and then click a point even with the left edge of the Product label and about two grid intervals below it.
Access opens a blank, unbound subreport in the main report and displays the first page of the SubReport Wizard.
Make sure that the Use existing Tables and Queries option is selected, and then click Next.
Click the down arrow to the right of the Tables/Queries box, and then click Query: Sales by Category.
Double-click CategoryID, ProductName, and ProductSales to move them from the Available Fields list to the Selected Fields list, and then click Next.
Make sure the Choose from a list option is selected, and click Next.
Accept the suggested name, Sales by Category subreport, and click Finish.
The Sales by Category subreport takes the place of the unbound subreport in the main report.
Click the subreport control, and press
to display the Properties dialog box, if necessary.On the Format tab, change the width of the subreport to
5"and the height to1.5". Then press
.In the subreport, right-click the Report Header section selector, and click Report Header/Footer on the shortcut menu to delete the header and footer. Click Yes in the alert box.
In the subreport, right-click the Page Header section selector, click Page Header/Footer on the shortcut menu, and click Yes in the alert box.
The subreport now has only a Detail section.
In the Detail section, click the CategoryID text box, and then press the
key.Click the ProductName text box, and change its width to
2.125".Click the ProductSales text box, and change its Left property to
2.3"and its Width property to1".The labels overlap.
In the main report, click the partially hidden Sales by Category subreport label, and delete it.
Click the selector in the upper-left corner of the subreport twice (the first click selects the subreport, the second puts a black square in the selector), and then on the View menu, click Report Header/Footer to display those sections.
Scroll the subreport to display the Report Footer section (you might have to adjust the Report window to see the scrollbar), click the Text Box control in the toolbox, and then click in the center of the Report Footer section grid.
Access inserts an unbound control and its label. You will use this control to perform the calculation.
In the Properties dialog box, change the label’s caption to
Total:, and set the Font Name property to Arial, the Font Size property to 9, and the Font Weight property to Bold.Click the unbound text box control, in the Properties dialog box click the Data tab, click Control Source, and click the ... button to open the Expression Builder.
In the first column, double-click Functions, and click Built-In Functions. Then scroll down in the third column, and double-click Sum.
Access displays Sum (≪expr≫) in the expression box.
Click ≪expr≫, click Sales by Category subreport in the first column, and double-click ProductSales in the second column.
Click OK to close the Expression Builder, and then press
to enter the calculation in the unbound text box in the Properties dialog box.Click the Format tab, and set the font properties as you did in step 18.
At the top of the Format tab of the Properties dialog box, click the down-arrow to the right of the Format box, click Currency, and then press
.Now the results of the calculation will be displayed as currency.
Position and adjust the size of the calculated control and its label to match those in the Detail section. Drag the left edge of the label to 1.1″ and the right edge of the text box to 3.2 inches.
Save your changes, and switch to Print Preview to see the results.
Switch to Design view, and click the subreport to select it.
Tip
Several factors affect the layout of the subreport in the main report when it is displayed in Print Preview. The width of the subreport sets the width of the space available for the display of text. The height of the subreport sets the minimum height of the area where product information is displayed (because the Can Shrink property for the subreport is set to No). The maximum height of the product display area is the length of the list (because the Can Grow property is set to Yes) plus the space between the bottom of the subreport and the bottom of the Detail section.
On the Format tab of the Properties dialog box, change the Border Style property to Transparent.
Save your changes, preview the report, and then close it.
CLOSE the GardenCo database.