Running the Report Wizard

The first step in creating almost any report is to run the Report Wizard - especially if you want to create a report that groups data using one or more fields, with headings or subtotals for each group. When the wizard finishes, you can switch to Design view and add your own formatting touches.

One big advantage of using the Report Wizard is that you can choose fields for the report from more than one table or query - you don't have to gather all the data you want into one query. For example, using the MOM sample database (which stores order and customer information for a mail-order store), you may want to create a report that lists all the orders for each customer. The information for this report comes from several tables: Address Book (which stores one record for each customer, including name and address), Orders (with one record for each order, including the order date), and Order Details (with one record for each item in an order, including the quantity ordered and the price per item).

If you want your report to include a calculated field, you need to create a query that calculates the field. For example, for our customer-order listing, we need the extended price (price x quantity) for each item in each order, so we can calculate the total amount of each order. Alternatively, you can add a calculated control to your report in Design view. (See the "Formatting Tips and Tricks" section, later in this tutorial, for a look at how this works.)

The Report Wizard asks different questions depending on the data in the record source and on options you select, so don't be surprised if you don't see every window each time you run it. Follow these steps to create a report:

1. Click the Create tab of the Ribbon. Then click the Report Wizard button in the Reports group.
   Access displays the first Report Wizard window.
2. Use the Tables/Queries drop-down list to select the table or query that stores the records you want to include in the report.
   If you plan to use information from several tables or queries, choose one of them. The Available Fields box lists the fields in the selected table or query.
3. Select the fields you want to display in the report in the Available Fields box and add them to the Selected Fields list by clicking the > button.
   Double-clicking a field name also adds it to the Selected Fields list. Click the >> button to add all the fields.
4. Repeat Steps 2 and 3 for fields in other tables or queries until all the fields you want to include in the report appear in the Selected Fields list.
   You can use some fields from tables and other fields from queries. For our customer-order listing, we select fields from the Address Book table, the Orders table, and the Order Details Qry query (which includes the Ext Price field, a calculated field that equals Price x Qty).
5. Click Next to see the wizard's next window.
   Access gives you a chance to choose how you want to group the data. For example, in our customer order report, grouping by customer is a good idea in order for all the information about one customer to be together. Within the section for each customer, the secondary grouping is by order, so that all the items in each order are listed together.
   If your report includes fields from more than one table or query, Access makes an educated guess about how you want to group your data, based on the relationships among the source tables. The report includes records from the Address Book table (the customer list), the Orders table, and the Order Details Qry query, and Access has automatically created three ways of grouping your data: one by customer (Address Book table), one by order (Orders table), and one by Order Details Qry. Click an option in the How Do You Want to View Your Data list and look at the example on the right side of the window. For the selected way of grouping your data, Access shows how it plans to arrange your data in the report.
6. Click the option that's the closest to the way you want to organize your report, and click Next.
   The guesses Access makes about how to group data are not always on the mark, so the wizard gives you a chance to make your own changes.
7. Add or change the grouping fields if you want, and then click Next.
   The wizard displays the grouping levels that you chose in the previous step on the right side of the window. To add an additional level of grouping, select a field from the list and click the > button. You can remove it by selecting it and clicking the < button. Here are some handy things to know about groups:
   • You can't change the groupings that the wizard created in Steps 5 and 6. Instead, you have to click the Back button and choose a different way to group your records.
   • After you add a field, you can change the importance (grouping level) of a field by selecting the field and clicking the up-arrow and down-arrow Priority buttons.
   • In the customer-order report, the wizard's suggested groups are right, so you wouldn't have to make any changes.
   • Clicking the Grouping Options button (which is not always available, depending on your groupings) displays the Grouping Intervals dialog box. There you can specify exactly how to group records using the fields you choose.
   • For date fields, you can group by day, month, or year. For number fields, you can group by 10s, 50s, 100s, 500s, 1,000s, 5,000s, and 10,000s so you can categorize values by magnitude.
   • For text fields, you can group on the first 1, 2, 3, 4, or 5 characters. Click OK to exit the Grouping Intervals dialog box and return to the main wizard window.
8. Choose how you want to sort the records within the lowest-level grouping, and click Next.
   Access automatically sorts by the fields on which you are grouping records. For example, if you are grouping records by customer and then by order, the customers appear in alphabetical order by name or in order of customer number. Within the lowest level of grouping, you can choose what order the records appear in - and specify up to four fields on which to sort. If you aren't grouping your records at all, you can still sort them here.
   Click in the 1 box, choose a field, and click the Ascending button if you want to switch to a descending sort. Additional sort fields are used only when the 1 sort field is identical in two or more records - in which case, the 2 field is used. If the 1 and 2 fields are identical in two records, Access sorts by the 3 and then the 4 field.
   In the customer-order report, you don't need to sort your records - the groupings take care of all the sorting you need.
9. Click the Summary Options button if you want to print counts, averages, or totals; specify which numeric fields to summarize; choose between the Detail and Summary and the Summary Only options; then click OK and click Next.
   Access displays a list of the numeric fields in your report, with a check box for Sum (total), Avg (average), Min (minimum or smallest value), and Max (maximum or largest value). If you want only the summary values, without information for individual records, click the Summary Only radio button. If you want Access to calculate the percent of the total that each grouping represents (for example, the percentage of orders that each customer represents), click the Calculate Percent of Total for Sums check box.
   In the customer-order report we're cooking up here, you click the Sum check box for the Ext Price field if you want to get a total of the items in each order and for each customer.
10. Choose the layout for your report from among the Access canned layouts, and click Next.
    You can preview the layout options by clicking one of the Layout radio buttons. The sample box on the left changes to show what your chosen layout looks like. If you want to print your report sideways on the paper, click the Landscape radio button.
11. Choose the style - typefaces, colors, lines, and boxes - for your report, and click Next.
    Access has many preset styles to choose from. Click a style to see a sample.
12. Type a title for the report. Choose whether to display the report in Print Preview or in Design view, and click Finish.
    The title appears at the top of the report. The Report Wizard takes a moment to create the report, and then displays it in the view you chose.

The report may look close to perfect, or it may look like a complete wreck. For example, the customer-order report as created by the Report Wizard contains the right information. Luckily, you can switch to Design view to fix it up. Click the View button on the Ribbon or choose View → Design View.