Anatomy of a Report

In Access, most reports are divided into distinct sections. Figure 13-1 shows a simple report in Design View. As you can see, the main data is located in the Detail section. This section repeats for each record in the source data.

Figure 13-1: Report in Design View

As the names imply, the page header and page footer appear at the top and bottom of each page of the report, and the report header and report footer appear at the beginning and end of the report.

Interestingly, the Page and Report sections can be turned off using the View menu option when a report is open in Design View. However, you must be careful here. If you do turn them off, Access is not just hiding them; it is physically deleting them. If you turn them back on at some future point, any content in them will be lost.

Access uses a default template to create reports when using the AutoReport feature. However, you can use a customized form to serve as a template by using the Tools | Options menu selection. You can also import report objects from an outside source by using File | Get External Data | Import. Once imported, you can use Tools | Options to set it as the template.

You can easily adjust the size of each section by dragging the divider bars. In addition, you can create customized headers and footers based on groupings. Select View | Sorting and Grouping to open the Sorting and Grouping dialog box. Here it is shown as if you were going to group the records on the txtState field:

Notice that the Group Header is turned on. In addition, not only will the report group on the txtState field, but it will sort by that column in ascending order.

If you close this box now, the Design View of the report will reflect the new grouping section, as shown in Figure 13-2. You could then drag the txtState column, and related label, up to that grouping section. If you wanted to, you might have another grouping within the txtState grouping that reflected, perhaps, the txtZipCode column. The possibilities are many.

Figure 13-2: Report with new header grouping added

As an alternative to the AutoReport tool, there is a Report Wizard that takes you through the report-building process step-by-step. In most cases, the wizard gives you a nice report with, if you choose it, groupings.

You may need to make some adjustments after using it. For instance, the name you give the report is the name that appears in the title. If you follow naming standards, you might use a name like rptCustomer. Do you want that to appear as the title of the report? Probably not!

What I do is name the report as I want the title to appear at the top of the report. Then, once the report is created, I rename it in the Database window. The report title retains the original name. As an alternative, you can go into Design View and manually change the title.

There are also specialized report capabilities for things like mailing labels and organizer pages. We’ll look at examples of charts and mailing labels later in the chapter.

Because of all this report-writing capability, the need to create reports with VBA is actually minimized. Let’s take a look at several scenarios using the wizard, and then using VBA.