The Report Wizard that Access 2010 provides to assist you in constructing reports is similar to the Form Wizard you used earlier to create forms. To practice using the Report Wizard, we’ll build the Contact Events report again. Click the Navigation Pane menu, click Object Type under Navigate To Category, and then click Queries under Filter By Group. Select the qryRptContactEvents query in the Navigation pane, and then click the Report Wizard button in the Reports group on the Create tab to open the Report Wizard.
On the first page of the Report Wizard, shown in Figure 17-20, select the fields you want in your report. (If you have a table or query selected in the Navigation pane and then click Report Wizard, Access automatically uses that object as the record source for the report.) You can select all available fields in the order in which they appear in the underlying query or table by clicking the double right arrow (>>) button. If you want to select only some of the fields, or if you want to specify the order in which the fields appear in the report, select one field at a time in the Available Fields list and click the single right arrow (>) button to move the field to the Selected Fields list. If you make a mistake, you can select the field in the Selected Fields list and then click the single left arrow (<) button to move the field to the Available Fields list. Click the double left arrow (<<) button to remove all selected fields from the list on the right and start over.
To create the Contact Events report, you should select all the fields. Then click Next to go to the next page.
Inside Out: Selecting Fields from More Than One Table and/or Query
You can also select fields from one table or query and then change the table or query selection in the Tables/Queries list. The Report Wizard uses the relationships you defined in your database to build a new query that correctly links the tables or queries you specify. If the wizard can’t determine the links between the data you select, it warns you and won’t let you proceed unless you include data only from related tables.
The wizard examines your data and tries to determine whether there are any natural groups in the data. Because this query includes information from the tblContacts table that has a one-to-many relationship to information from the tblContactEvents table, the wizard assumes that you might want to group the information by contacts (the ContactID, Contact, and Phone fields), as shown in Figure 17-21. If you don’t want any report groups or you want to set the grouping criteria yourself, select By tblContactEvents. In this case, the Report Wizard has guessed correctly, so click Next to go to the next step.
Figure 17-21. Make sure to verify the primary grouping criteria on the second page of the Report Wizard.
On the next page (shown in the background in Figure 17-22), the Report Wizard shows you the grouping already selected for ContactID and asks whether you want to add any grouping levels below that. (If you chose to set the criteria yourself by choosing By tblContactEvents on the previous page, you will see a similar window with no first group selected.) You can select up to four grouping levels. The wizard doesn’t allow you to enter an expression as a grouping value—something you can do when you build a report from scratch. If you want to use an expression as a grouping value in a report that you create with the Report Wizard, you have to include that expression in the underlying query. For this report, you could also group within each contact by the ContactDateTime field, so select that field and click the single right arrow to temporarily add it as a grouping level.
When you add grouping levels, the Report Wizard makes the Grouping Options button available for those levels. You can select the ContactDateTime By Month grouping level on the right side of this page and then click this button to see the Grouping Intervals dialog box, shown in Figure 17-22. For a text field, you can group by the entire field or by one to five of the leading characters in the field. For a date/time field, you can group by individual values or by year, quarter, month, week, day, hour, or minute. For a numeric field, you can group by individual values or in increments of 10, 50, 100, 500, 1,000, 5,000, or 10,000. As you can see, the Report Wizard has automatically assumed grouping by month when you added the ContactDateTime field as a grouping level. You don’t need that grouping level in this sample, so cancel the Grouping Intervals dialog box, select ContactDateTime By Month on the right side of the page, and click the single left arrow to remove it. Then click Next.
On the next page, shown in Figure 17-23, the Report Wizard asks you to specify any additional sorting criteria for the rows in the Detail section. (Access will sort the report at this point by the grouping level fields you specified on the previous page.) You can select up to four fields from your table or query by which to sort the data. By default, the sort order is ascending. Click the button to the right of the field selection list box to switch the order to descending. You can’t enter expressions as you can in the Group, Sort, And Total pane. In this report, click the arrow to the right of the first box and select the ContactDateTime field. Click the button to the right once to switch it to Descending, as shown in the figure.
Click Summary Options to open the dialog box shown in Figure 17-24. Here you can ask the Report Wizard to display summary values in the group footers for any numeric fields the wizard finds in the Detail section. In this case, the Report Wizard sees that the ContactFollowUp field is the only one in the Detail section that is a number (a Yes/No data type). As you’ll see later in this chapter, the Report Wizard automatically generates a count of the rows, which explains why Count isn’t offered as an option.
Select the Sum check box for this field. (You can add the minus sign after the wizard is done to get the correct count.) Note that you also have choices to calculate the average (Avg) of values over the group or to display the smallest (Min) or largest (Max) value. You can select multiple check boxes. You can also indicate that you don’t want to see any detail lines by selecting the Summary Only option. (Sometimes you’re interested in only the totals for the groups in a report, not all the detail.) If you select the Calculate Percent Of Total For Sums check box, the Report Wizard will also display, for any field for which you have selected the Sum check box, an additional field that shows what percent of the grand total this sum represents. When you have the settings the way you want them, click OK to close the dialog box. Click Next in the Report Wizard to go on.
Figure 17-24. Click the Summary Options button on the fourth page of the Report Wizard to select additional summary options.
On the next page, shown in Figure 17-25, you can select a layout style and a page orientation for your report. When you select a layout option, the Report Wizard displays a preview on the left side of the page. In this case, the Outline layout option in Portrait orientation will come closest to the hand-built report you created earlier in this chapter. You should also select the check box for adjusting the field widths so that all the fields fit on one page (selected by default).
Click Next to go to the final page of the Report Wizard, shown in Figure 17-26. Here, you can type a report title. Note that the wizard uses this title to create the report caption that is displayed in the title bar of the window when you open the report in Print Preview, the label that serves as the report header, and the report name. It’s probably best to enter a title that’s appropriate for the caption and label and not worry about the title being a suitable report name. If you’re using a naming convention (such as prefixing all reports with rpt as we’ve done in the sample databases), it’s easy to switch to the Navigation pane after the wizard is done to rename your report. In this case, enter Contact Events as the title.
Select the Preview The Report option on the final page of the Report Wizard, and then click Finish to create the report and display the result in Print Preview, as shown in Figure 17-27. One of the first things you will notice is that Access has created alternating background colors for the detail lines to make it easier to see the data that goes with each record. This feature can be very useful if reports have a lot of information in the detail records and if the lines are packed close together.
It’s easy to use Design view or Layout view to modify minor items (such as adjusting the width and alignment of the ContactDateTime and ContactEventDescription fields and resizing the labels) to obtain a result nearly identical to the report you constructed earlier. You can see in Figure 17-27 that the ContactDateTime field displays # symbols for all the records. Access displays # symbols for date/time and numeric fields when it cannot display all the data in the control, but only when you select the Check For Truncated Number Fields check box under Application Options in the Current Database category of the Access Options dialog box. You need to fix the expression in the text box that calculates the Sum of the ContactFollowUp field and change the format to display the number. (The Report Wizard set the format to Yes/No.) You should also change the Sum label associated with this calculation. You can find the Report Wizard’s report at this point saved as rptXmplContactEvents2 in the sample database. As you might imagine, the Report Wizard can help you to get a head start on more complex report designs.