Report Architecture and Creation Strategies

Access is a popular reporting engine because Access reports are easy to create and update. You can easily deploy them to non-Access users using the Snapshot format (this feature is covered later in the chapter). Because it's so easy to link Access to record sources from other databases, Access development commonly features report generation for ODBC and other legacy data sources as well as traditional Access databases.

Report Sections

One feature that makes Access such a popular database-reporting engine is its banded report sections. The standard default report design includes three sections: the Detail, Page Header, and Page Footer sections. The Detail section is bordered above and below, respectively, by the Page Header and Page Footer sections. The Page Header and Page Footer sections each appear once per page within a report. The Detail section repeats once for each record in a report's record source. In a simple tabular report, the Detail section is likely to repeat multiple times per page.

When you create a new report, Access relies on the current report template to determine the types of sections in the report, as well as the dimensions and other default property settings. When first installed, Access uses its Normal report template. However, you can import any other report in the current or another Access database file or Access project to use as the default template. Recall that an Access project is the client application of a Microsoft Access and SQL Server client/server solution (this concept is covered in more detail in Chapter 11). Choose Options on the Tools menu in the database window and select the Forms/Reports tab to expose the text box to specify a report template other than Normal. Changes to the Report Template text box on the Options dialog box will affect all future reports. Previously created reports retain the design specified in the Report Template text box when they were created unless you explicitly alter their design.

Note  

The Options command on the Tools menu in the database window displays the controls for setting the form template and the report template. You can use the Form Template text box just as you would use the Report Template text box to control the default design of a form.

The View menu in the report design window offers two commands for adding and removing sections within the report when it is open in Design view. These menu commands let you add or remove a pair of header/footer sections for the page or for the entire report. The menu items have toggle controls, but they do not simply toggle the visibility of a section; once you remove a section, you cannot use the menu to restore the section's content. Even if you do restore a section, its former content will be lost. The Page Header/Footer command on the View menu adds or removes the Page Header and Page Footer sections. The Report Header/Footer command adds or removes the Report Header and Report Footer sections. The Report Header and Report Footer sections appear once in each report ”at the beginning and at the end, respectively. You can manually resize any section in a report by dragging its bottom edge up or down or dragging its right edge away from or toward its left edge. When you drag a section's right edge, you modify the size of all the report's sections ”not just the one you are manipulating.

You can create custom header and footer report sections for groups that you create with the Sorting And Grouping command on the View menu in the report design window. The custom header sections are often convenient for providing information at the beginning of a group , while custom footer sections make a good location for unbound report controls that display subtotals for the records in a group.

Creating Reports

You can create reports both manually and programmatically. The AutoReport Wizard works the same way as the AutoForm Wizard ”both wizards are available as selections from the New Object tool on the Database toolbar. Select any row-returning record source in the Database window. Then, just click the New Object: AutoReport button on the Database toolbar. Although the AutoReport Wizard functions identically to the AutoForm Wizard, you might find the resulting layout unappealing as a starting point for programmatic customization. The default columnar layout without a delimiter between records makes it hard to discern where one record ends and another begins.

The New Report dialog box offers additional report wizards, such as the AutoReport: Tabular Wizard. You can open the dialog box by selecting Reports in the Objects bar on the Database window and clicking the New button on the Database window. The AutoReport: Tabular Wizard provides a more typical layout for database reports than the columnar layout generated by the AutoReport Wizard on the Database toolbar. Before clicking OK on the New Report dialog box to invoke the AutoReport: Tabular Wizard, you must use a drop-down control on the dialog box to specify a record source, such as a query, for the report. This wizard displays each record as a separate row in the Detail section. The wizard utilizes the Page Header section to display labels for the columns that appear on each page. If there are too many columns to fit on one page, the wizard positions the columns for a single record across two or more pages.

The Report Wizard provides a richer graphical interface than the AutoReport: Tabular Wizard for designing single-column and multicolumn reports. For example, you do not need to specify a record source before clicking OK to launch this wizard from the New Report dialog box, because the wizard allows you to specify a record source from its own pages. The Report Wizard is especially convenient for designing reports that generate grand totals and subtotals for numeric fields. You can also use this wizard to create groups and specify sort orders for the rows of a report. A report's sort settings override any SQL instructions that specify the sort order for the report's record source.

Other specialized wizards you can use to create reports include the Label Wizard and the Chart Wizard.

The New Report dialog box also lets you enter Design view to create a new report. This affords more flexibility than the wizards do, but it requires a more advanced understanding of Access report design as well as a mastery of the Design view menu for reports. If you plan to program reports, you should familiarize yourself with this view. You can also enter Design view for an existing report, such as a report created with a wizard or one that was previously created manually and requires editing. To open an existing report in Design view, select its name in the Database window. Then, click the Design control on the Database window.

Finally, you can programmatically create reports, which provides substantial flexibility and permits you to make report design responsive and interactive. You can also programmatically manipulate existing reports to edit them permanently or to dynamically update them in response to user input or data values.