Getting to Know Microsoft Access, Part 8: Learning about Reports

Overview

Reports utilize the same basic elements as forms; the Design view of the two objects is remarkably similar. Reports use controls, properties, and so forth, just like forms. They are very different concepts however: forms work with data and data management tasks such as data entry and validation, while reports present data as information.

You can create your first report by choosing the Insert | Report menu option. You’ll be presented with a dialog asking you to select the method you’ll use to create the report: Design View, Report Wizard, the AutoReports, Chart Wizard, or the Label Wizard. The AutoReports give you a snapshot of all the data in the tables upon which you base the AutoReport. AutoReports are not complicated, but they’re useful for checking your data.

Report Wizard

The Report Wizard is similar to the Form Wizard. This Report Wizard steps you through a series of dialog boxes that you must complete in order to create a report. There are some things you can do with the Report Wizard that you can’t do with the Form Wizard. For example, the Report Wizard gives you options such as: sorting, grouping, and summarizing. You create a single-table report with the report wizard by choosing your table in the report wizard dialog boxes.

Additionally you can create a multiple-table report by selecting a main table and subordinate tables in the Report Wizard. To do this, just choose both tables you want to base your report on in the dialog boxes in the report wizard; the wizard will automatically create a hierarchy for you. Make sure your tables are related before you try to base a report on them, or you’ll get errors.

When you want to create a report that uses monetary or numeric values you can use the summarizing options. Summarizing allows you to find the minimum, maximum, or average of a group of values, or to compute total values and percentages. You can choose detailed summaries or just the standard summary. Finally, you can check a box with the label "Calculate percent of total for sums" to show on the report what percent each calculated sum is of the total calculated sum.

Once you’ve created your report, you can preview how it will look when it prints or you can change its appearance in the design view. The report design view is almost identical to the form design view and gives you the same options.

You can change the style for your reports by choosing the Format | AutoFormat menu option. Using this method you can change the font, color, and border formatting for your reports.

You can add page numbers to your reports by choosing the Insert | Page Numbers menu option. You can edit the format of the page numbers by entering a custom expression in the control source property box for the page numbers field. For example, the following expression: =[Page]&”/”&[Pages]&”Pages” makes the page numbers on each page of the report appear as 1/5, 2/5, 3/5, 4/5, or 5/5 depending on what page you are currently viewing (the first number) and the number of total pages in the report (the second number).

You can create report snapshots – basically electronic versions of reports you can distribute by email instead of by paper. Just choose the name of the report you want to use and choose the File | Export menu option. Choose the Snapshot Format in the Save as type box when the Export Report As Dialog box opens up.

Customizing Your Report

You can change your report to look almost any way you’d like. The best way to get started customizing a form is to use the Create report in Design view shortcut.

You can add a header or footer to your report by choosing the View | Page Header/Footer menu option. You can add images to your report, calculated fields, OLE objects, hyperlinks, and background pictures, just as you would do in a form design.

You can base a report on a Parameter Query. This allows you to specify the criteria for the report when you run it, just as you would if you ran the query directly. Create a Parameter Query by first creating the Parameter Query, as described in a previous lesson, and then creating the report. Create the report by selecting the query in the Database window and choosing the Insert | Report menu option. Access will immediately prompt you for the parameters. Enter the parameters and preview the report. You can print the specified parameters in the report header by clicking the Text Box control tool and placing a text box control in the report header; then deleting the attached label; and finally, opening the property sheet of the new control and entering an appropriate expression that matches the Parameter Query’s criteria expression.

You can sort and group your report records. Then you can summarize the sorted or grouped information to illustrate trends and make conclusions. You can change the underlying sort order, grouping records by text, number, currency, autonumber, or date/time field types. Each data type allows you to group the records differently. To change the Sort Order property, keep the Order By On property set to "Yes" and change the Order By property in the following ways to suit your needs: type the field name in brackets followed by the abbreviation ASC to sort the records by one field in ascending order; type the field name in brackets followed by DESC to sort the records in descending order.

You can group records as well. For example, you might base a report on a query that only uses records from tables that have values in a shared field. You can then group those records in your report by specific values in that shared field. For example, if grouped by a "year" field you might have certain records in 2002, others in 2003, and others in 2004. When you want to create the grouped report you just choose the View | Sorting and Grouping menu option and select a field to group those records by in the dialog box. Then, select the order in which you want to arrange the groups. Select the group properties in the lower pane, and finally select whether or not to add a group header or footer to the sections. After you add a header or footer you can show the field you’re grouping by in the header by just cutting and pasting a label to the group header. Then, to add a text box control that shows the value for the group, just use the Text Box control tool and enter the appropriate expression in the property sheet for that control. When you’ve done that, select the label and replace the text in the label to fit your needs.

More Ways to Customize Your Reports

Count summaries count the number of records in each group and then show those summaries wherever you want in your report. You can add a count summary to the footer of a report by opening the report in Design view and creating a text box control in the group footer. In the property sheet of the control type: =Count([Entry]).

A running total calculates the accumulated values in a group and resets the value to 0 for the next group, and then does the same thing to the following group. Simply add a running total by selecting the appropriate checkboxes when you create a calculated control. Set the Running Sum property to either Over Group or Over All.

Subreports are complete reports inserted into the main report. Use the Subform/Subreport control to create a subreport in the report design. When you click to create the Subreport, you will be given a series of dialog boxes from which to choose options for your subreport, such as the fields you wish to show in the subreport from your tables or queries.

You can create multiple column reports by first specifying a columnar layout in the Report Wizard. Then, using Page Setup, you can specify as many columns as you like.

Printing Mailing Labels and Envelopes is easy in Access. Since you can fit many labels on one page of printed paper you’ll design your labels in columnar layout. To get started, let’s use the Label Wizard. Just open the New Report dialog box and select Label Wizard. In the following dialog boxes you’re able to specify the Unite of Measure, the Label type, and the brand of label. You’ll also be able to select your font type, size, weight, and color. You can also specify underlining and italics. Finally you’ll need to move the fields to the prototype label, add spaces, punctuation, and other characters you need.