Creating a Grouping Report with the Report Wizard

This section shows you how to use the Report Wizard to create a grouping report based on data in the Products and Suppliers tables of the Northwind Traders sample database. (Like the Form Wizard, the Report Wizard allows you to create reports that contain data from more than one table without first creating a query.) This report displays the quantity of each specialty food product in inventory, grouped by product category.

To create an inventory report, you modify the basic report created by the Report Wizard. The process of creating a basic report with the Report Wizard is similar to the process that you used to create a form in Chapter 12, "Creating and Using Forms." An advantage of using the Report Wizard to introduce the topic of designing Access reports is that the steps for this process are parallel to the steps you take when you start with a default blank report. Chapter 15, "Preparing Advanced Reports," explains how to start with a blank report and create more complex reports.

To create a Products on Hand by Category report, follow these steps:

1.	Click the Reports tab in the Database window, and then click the New button. Access displays the New Report dialog (see Figure 14.2). Figure 14.2. The New Report dialog, in which you select the report's type and data source.
2.	Like forms, reports require a data source, which can be a table or query. Select the Products table from the choices offered in the New Report dialog's drop-down list (refer to Figure 14.2). Select Report Wizard in the list in the dialog's upper-right corner and click OK. The Report Wizard displays its opening dialog.
3.	The fields that you choose to display represent rows of the report. You want the report to print the product name and supplier so that users do not have to refer to another report to associate codes with names. The fields from the Products table that you need for this report are CategoryID, ProductID, ProductName, SupplierID, and UnitsInStock. With the > button, select these fields in sequence from the Available Fields list (see Figure 14.3). As you add fields to the Selected Fields list, Access removes the field names from the Available Fields list. Alternatively, you can double-click the field name in the Available Fields list to move the field name to the Selected Fields list. The fields appear from left to right in the report based on the top-to-bottom sequence in which the fields appear in the Selected Fields list. Figure 14.3. Selecting the fields of a report from one or more tables or queries in the Report Wizard's opening dialog.
4.	To avoid having to look up Supplier ID numbers in a separate report, you need to add to this report the CompanyName field from the Suppliers table. Open the Tables/Queries drop-down list and select Table: Suppliers (refer to Figure 14.3).
5.	Instead of presenting the supplier name as the report's last field, you want the report's CompanyName column to follow the SupplierID report column. Select the SupplierID field in the Selected Fields list. Now select the CompanyName field from the Available Fields list and click the > button. Access moves the CompanyName field from the Available Fields list and inserts the field into the Selected Fields list, after the SupplierID field and before the UnitsInStock field (refer to Figure 14.3). Choose Next to continue with the second Wizard dialog, shown in Figure 14.4. Note If you want to change the field order shown in the right pane of Figure 14.4, use the button to move the field back to the Available Fields list. You can retrace your steps to correct an error by clicking the Back button whenever it is activated. The Finish button accepts all defaults and jumps to the end of the wizard, so you shouldn't use this button until you're familiar with the Report Wizard's default selections.
6.	The Report Wizard asks you to choose how you want to view the data in the report. Notice the Show Me More Information button near the left center of the wizard dialog. Click this button to display the first of a series of hint dialogs for the Report Wizard (see Figure 14.5). If you click the Show Me Examples option, Access displays additional hint screens. These screens use examples from the Sales Reps, Customers, and Orders tables to show you the different groupings that the Report Wizard can automatically add to the report. Click the Close button repeatedly until you return to the Report Wizard dialog shown in Figure 14.4. Figure 14.4. Choosing how you want to view your data in the second Report Wizard dialog. Figure 14.5. The first of the Report Wizard's hint screens.
7.	For this report, you select your own groupings. Select By Products in the list, and choose Next to continue with the third Report Wizard dialog.
8.	The Report Wizard asks whether you want to add any grouping levels to the report. Select the CategoryID field in the list, and click the > button to establish the grouping By Products category. The Report Wizard dialog now appears as shown in Figure 14.6.
9.	Click the Grouping Options button. The Report Wizard displays the Grouping Intervals dialog shown in Figure 14.7. By changing the grouping interval, you can affect how Access groups data in the report. For numeric fields, you can group items by 10s, 50s, 100s, and so on. For text fields, you can group items based on the first letter, the first three letters, and so on. Figure 14.6. Selecting grouping levels for your report. Figure 14.7. Selecting grouping intervals for your report. Note If your application uses a text-coding scheme, such as BEVA for alcoholic beverages and BEVN for nonalcoholic beverages, you can combine all beverages in a single group by selecting 1st 3 Characters from the Grouping Intervals list. Access 97 provides this option for numeric fields and for fields of the Text data type.
10.	This report doesn't require any special grouping interval, so select Normal in the Grouping Intervals list, and click OK to return to the Report Wizard's third dialog (refer to Figure 14.6). Choose Next to continue with the fourth wizard dialog.
11.	You can sort the records within groups by any field that you choose (see Figure 14.8), with up to four different sorted fields. The dialog does not offer CategoryID as a choice because the records are grouped in this field, and the field on which the grouping is based is therefore automatically sorted. Select ProductID in the first drop-down list. By default, the sort order is ascending; if you want a descending sort order, click the button to the right of the drop-down list. (This button is a toggle control; click it again to return to an ascending sort.) Figure 14.8. Selecting a sort order for fields within groups.
12.	Choose the Summary Options button to display the Summary Options dialog (see Figure 14.9). If you want to add summary information to a report column, set the options for that column in this dialog. The Report Wizard lists all of the numeric fields on the report that aren't AutoNumber fields and offers you check boxes to select a Sum, Average, Minimum, and Maximum for that report column. Depending on the check boxes that you select, the Report Wizard adds those summary fields to the end of the report. Figure 14.9. Choosing the summary data you want included in your report in the Summary Options dialog of the Report Wizard. The Show option group allows you to select whether the report shows the summary fields only or the full report with the summary fields added at the end of each group and at the end of the report. For this report, select the Sum and Avg check boxes, the Detail and Summary option, and the Calculate Percent of Total for Sums check box. (The Calculate Percent of Total for Sums check box displays the group's total as a percentage of the grand total for all groups.) Click OK to return to the Report Wizard dialog.
13.	Choose Next to continue with the fifth Wizard dialog, shown in Figure 14.10. The Report Wizard asks you to select one of six layout styles for your report. The window in the left side of the wizard dialog shows a preview of the layout style that you select. For this report, choose Stepped in the Layout option group.
14.	By default, the Report Wizard selects the Adjust the Field Width So All Fields Fit on a Page check box. As a rule, you should select this option to save paper and make your report more legible. In the Orientation option group, you select the report's printing orientation. Make sure that you select the Portrait option. Choose Next to continue with the sixth Report Wizard dialog.
15.	Select one of the predefined report styles for your report. The window on the left shows a preview of the selected style (see Figure 14.11). (You can customize or create your own styles for the Report Wizard to use. This activity is described in the Using AutoFormat and Customizing Report Styles section later in this chapter.) Select the Compact style, and then choose Next to continue with the seventh and final Report Wizard dialog. Figure 14.10. Choosing a layout format for a report.
16.	Type Products On Hand by Category as the title for the new report; the Report Wizard also uses this title as the name of the saved report it creates (see Figure 14.12). Select the Preview the Report option and click Finish to complete your report specification. The Report Wizard creates the report and displays it in print preview mode. (To get Help with the report, click the Display Help on Working with the Report? check box.) Figure 14.13 shows the basic report that the Report Wizard creates. Use the vertical and horizontal scroll bars to position the preview as shown. (Leave the report in Print Preview mode for now because you use this report as the basis for examples in subsequent sections of this chapter.) Figure 14.11. Selecting a style for your report. Figure 14.12. Giving your report a name and title, and choosing how you want to view the completed report.

◂◂See Using Lookup Fields in Tables

In Figure 14.13, notice that the report appears to have duplicate columns—a Supplier column, which lists the name of the product's supplier, and the Company Name column, which also lists the name of the product's supplier. The reason for this duplication is twofold:

• A Caption property is set for the SupplierID field of the Products table. This property establishes an alias for the field. The Report Wizard substitutes the text in the Caption property for the field name in field labels on the report.

• The SupplierID field of the Products table is defined as a lookup field. Therefore, Access automatically looks up the value that corresponds to the SupplierID code in the Suppliers table and displays that value, rather than the actual code number stored in the field.

When you view the lookup field in Design view, a nonfunctional drop-down list button (see Figure 14.14 in the next section, Using Access's Report Windows) designates lookup fields. This report doesn't need to include the CompanyName field of the Suppliers table because the SupplierID field is a lookup field.

With a few simple modifications, you can obtain a finished report with the information necessary to analyze Northwind's current inventory. (See Modifying a Basic Report Wizard Report later in this chapter.)