Building Reports Based on More Than One Table

The majority of reports you create will probably be based on data from more than one table. This is because a properly normalized database usually requires that you bring table data back together to give users valuable information. For example, a report that combines data from a Customers table, an Orders table, an Order Details table, and a Product table can supply the following information:

Customer information, such as company name and address
Order information, such as order date and shipping method
Order detail information, such as quantity ordered and price
A product table, including a product description

You can base a multitable report directly on the tables whose data it displays, or you can base it on a query that has already joined the tables, providing a flat table structure.

Creating One-to-Many Reports

You can create a one-to-many report by using the Report Wizard, or you can build a report from scratch. Different situations require different techniques, some of which are covered in the following sections.

Building a One-to-Many Report by Using the Report Wizard

Building a one-to-many report with the Report Wizard is quite easy. You just follow these steps:

Click Reports in the Objects list and double-click Create Report by Using Wizard.
From the Tables/Queries drop-down list box, select the first table or query whose data will appear on the report.
Select the fields you want to include from that table.
Select each additional table or query you want to include on the report, selecting the fields you need from each (see Figure 17.12). Click Next.

Figure 17.12. The first step of the Report Wizard: selecting the fields you want on a report.
The next step of the Report Wizard asks you how you want to view the data (see Figure 17.13). You can accept Access's suggestion (By Orders), or you can choose from any of the available options (By Customers, By Orders, By Order Details, or By Products). Click Next.

Figure 17.13. The second step of the Report Wizard: designating how you want to view the data.
The next step of the Report Wizard asks whether you want to add any grouping levels. You can use grouping levels to visually separate data and to provide subtotals. In the example in Figure 17.14, the report is grouped by country and city. After you select grouping levels, click Next.

Figure 17.14. The third step of the Report Wizard: selecting groupings.
The next step of the Report Wizard lets you select how you want the records in the report's Detail section to be sorted (see Figure 17.15). This step of the wizard also allows you to specify any summary calculations you want to perform on the data (see Figure 17.16). Click the Summary Options button to specify the summary calculations. By clicking the Summary Options button, you can even opt to include the percentage of total calculations. Click OK when you're done adding the summary options.

Figure 17.15. The fourth step of the Report Wizard: selecting a sort order.

Figure 17.16. Adding summary calculations.
In the next step of the Report Wizard, you select the layout and orientation of the report. Layout options include Stepped, Blocked, Outline 1, Outline 2, Align Left 1, and Align Left 2. You can click the different option buttons to preview how each of the reports will look.
The next step of the Report Wizard lets you select from predefined styles for a report, including Bold, Casual, Compact, Corporate, Formal, and Soft Gray. You can preview each style to see what it looks like.
In the next step of the Report Wizard, you select a title for the report. The title also becomes the name of the report. I like to select an appropriate name and change the title after the wizard is finished. The final step also allows you to determine whether you want to immediately preview the report or see the report's design first. Click Finish when you are ready to complete the process.

The report created in this example is shown in Figure 17.17. Notice that the report is sorted and grouped by Country, City, and OrderID. The report's data is in order by ProductName within an OrderID grouping.

Figure 17.17. A completed one-to-many report.

graphics/17fig17.jpg

This method of creating a one-to-many report is by far the easiest. In fact, the "background join" technology that the wizards use when they allow you to pick fields from multiple tables—figuring out how to build the complex queries needed for the report or form—is one of the major benefits of using Access as a database tool. It's a huge timesaver and helps hide unnecessary complexity from you as you build a report. Although you should take advantage of this feature, it's important that you know what's happening under the covers. The following two sections give you this necessary knowledge.

Building a Report Based on a One-to-Many Query

A popular method of building a one-to-many report is from a one-to-many query. A one-to-many report built in this way is constructed as though it were based on the data within a single table. First, you build the query that will underlie the report (see Figure 17.18).

Figure 17.18. An example of a query underlying a one-to-many report.

graphics/17fig18.jpg

When you have finished the query, you can select it rather than select each individual table (as done in the previous section). After you select the query, you follow the same process to create the report as described in the preceding section.

Building a One-to-Many Report with the SubReport Wizard

You can build a one-to-many report by building the parent report and then adding a SubReport control. This is often the method used to create reports such as invoices that show the report's data in a one-to-many relationship rather than in a denormalized format. If you want to use the SubReport Wizard, you must make sure that you select the Control Wizards tool before you add the SubReport control to the main report. Here is the process:

Click to select the SubForm/SubReport control tool.
Click and drag to place the SubForm/SubReport control on the main report. You usually place the SubForm/SubReport control in the report's Detail section. When you place the SubForm/SubReport control on the report, the SubReport Wizard is invoked (see Figure 17.19).

Figure 17.19. Adding a SubForm/SubReport control to invoke the SubReport Wizard.
Indicate whether you want to base the subreport on an existing form or report or whether you want to build a new subreport based on a query or table. Click Next.
If you select Table or Query, you have to select the table or query on which you will base the subreport. You can then select the fields you want to include on the subreport. You can even select fields from more than one table or query. When you're finished making selections, click Next.
The next step of the SubReport Wizard suggests a relationship between the main report and the subreport (see Figure 17.20). You can accept the selected relationship, or you can define your own. When you're finished, click Next.

Figure 17.20. The SubReport Wizard: identifying the relationship.
The final step of the SubReport Wizard asks you to name the subreport. Click Finish when you're finished.

To follow standards, a subreport name should begin with the prefix rsub.

As you can see in Figure 17.21, the one-to-many relationship between two tables is clearly highlighted by this type of report. In the example in Figure 17.21, each customer is listed. All the detail records reflecting the orders for each customer are listed immediately following each customer's data.

Figure 17.21. A one-to-many report created with the SubReport Wizard.

graphics/17fig21.jpg

Working with Subreports

When you add a subreport to a report, it's important to understand what properties the SubReport Wizard sets so that you can modify the SubForm/SubReport control, if needed.

Subreport Properties

You should become familiar with the properties of a SubForm/SubReport control, which are described in the following sections (see Figure 17.22).

Figure 17.22. Properties of the SubForm/SubReport control.

graphics/17fig22.jpg

`Source Object`

The Source Object control specifies the name of the report or other object that's being displayed within the control.

`Link Child Fields`

The Link Child Fields control specifies the fields from the child report that link the child report to the master report.

`Link Master Fields`

The Link Master Fields control specifies the fields from the master report that link the master report to the child report.

Modifying Subreports

Not only should you know how to work with the properties of a SubForm/SubReport object, but you should also be able to easily modify the subreport from within the main report. You can always modify the subreport by selecting it from the list of reports in the Database window. To do this, you select the report you want to modify and then click Design. You can also modify a subreport by selecting its objects directly within the parent report.

Access 2002 and Access 2003 make it easier than earlier versions of Access to work with subforms and subreports in Design view. Scrolling has been improved so that it's easier than ever to design subforms and subreports. In addition, you can now open a subreport in its own separate Design view window by right-clicking the subreport and selecting Subreport in New Window. Or, instead of right-clicking the subreport, you can select the subreport and then click View | Subreport in New Window from the menu bar.

[ Team LiB ]

Building Reports Based on More Than One Table

Creating One-to-Many Reports

Building a One-to-Many Report by Using the Report Wizard

Figure 17.12. The first step of the Report Wizard: selecting the fields you want on a report.

Figure 17.13. The second step of the Report Wizard: designating how you want to view the data.

Figure 17.14. The third step of the Report Wizard: selecting groupings.

Figure 17.15. The fourth step of the Report Wizard: selecting a sort order.

Figure 17.16. Adding summary calculations.