Creating Reports

To print data from a group of records in one or more tables or queries in your Microsoft Access database in a customised format, you create reports.

What are reports?

Reports are not unlike forms in that information is presented in a customised manner. Hence, you will find the same sorts of designing tools for building or modifying a report just as you would when creating a form. However, the main difference is that a report shows more than one record on a page as a list, whereas a form only shows one record at a time.

In essence, a report is like the datasheet view for your tables. The only exception is that you have total control over which fields you want printed in your report and how it should look on a page.

Furthermore, you will also find within a report the ability to compute totals and averages from values stored in certain fields across many records. And you can also group and sort records into many different levels (i.e. sublists). This is the power behind reports using Microsoft Access.

How do I create a report?

To create a report:

1. Click the Database Window button on the toolbar if you are not already in the Database window mode.
2. Click the Report button in the Database window to view all reports.
3. Click the New button to begin creating a new report. The New Report dialog box appears.
4. To tell Microsoft Access where to get its data for creating a report, click the arrow on the popup menu to list all the tables and queries in your database.
5. Select the table or query to which you want to bind the data to the report.
6. Click the Blank Report button to start designing a report from scratch.
7. Now you are ready to customise your report in whatever way you like.

Or alternatively, the easiest way to create a report is to use the Report Wizard, and later you can modify the design of the report if you want. To use the Report Wizard, click the Report Wizard button in the New Report dialog box.

What do I do now?

When designing reports, you will be doing things like adding controls just like a form in Microsoft Access. You will be moving and sizing those controls; setting properties to the controls to do things like reducing white space; and even adding, deleting or sizing headers and footers.

For further information about controls, see the previous section on forms.

If you are going to add controls to a report, you will need to know where to put them.

Where can I place controls in a report?

A report is divided into five sections: a Report Header, a Page Header, a Detail (or Main Body) section, a Page Footer, and a Report Footer.

If you place a control in the Report Header or Report Footer section, that control will appear only at the beginning of the report (i.e. page 1 when the report is printed). If you want a control to appear at the top and/or bottom of each printed page, place it in the Page Header or Page Footer section respectively. If you want the control to appear each time a record is shown on a printed page, place the control in the Detail section.

A report in design view.

A report in normal view.

How do I add (or delete) headers and footers in a report?

Adding or deleting Page and Report Headers and Footers is the same as for forms. You will find them under the Format menu. Just choose Page Header/Footer or Report Header/Footer, and the page header and footer will come as a pair. If you want the header but not its complementary footer section, you can resize the footer section so that it has no height.

In addition to this, you can also define group headers or footers that group and display records with related values. This is the way to help you organise your records and clarify your report.

What's the way to add group headers and footers to a report?

To group records in a report:

1. Click the Sorting and Grouping button on the toolbar.
2. The Sorting and Grouping dialog box is opened. You can now specify the field(s) you want to group as either group headers or footers.

What about sorting records in a report?

Usually the records in the underlying table or query bounded to the report are not sorted. To sort records in a report:

1. Click the Sorting and Grouping button on the toolbar.
2. The Sorting and Grouping dialog box is opened. Click an empty cell in the Field/Expression column.
3. Click the button with an arrow in the empty cell to display the list of fields in the underlying table or query.
4. Select a field from the list.
5. Click the Sort Order column for that field.
6. Click the button with an arrow in the empty cell and choose whether you want the data in the field to be sorted in an ascending or descending manner.
7. If you ever need to delete or change the order of fields for sorting (i.e. manipulate the rows containing the field names), use the same technique as you would when manipulating fields (or rows) in table design view.
8. When finished, close the dialog box.

NOTE: There are numerous other properties you can modify under the Sorting and Grouping dialog box, such as controlling whether to automatically or manually position page breaks to prevent "orphaned" records appearing at the bottom of a report page. So play around and see what you can achieve.

How do I add a control to a report?

To add a control to a report such as a text box, you use the toolbox. To open the toolbox, click the Toolbox button on the toolbar. For further information about how to use this toolbox, see forms.

How do I insert the current date and page numbers in a report?

These are controls you can add to a report. You do this by adding a text box in the page header or footer, and then typing the following expression into it:

="Page " & Page & "of " & Pages

To display today's date on a report, type the following expression into the text box:

=Date()

Can I calculate the average and/or sum of the values of fields in records in a report?

You can do this by creating a calculated field and placing it in the Group or Page footer section of your report. To display a group total or average:

1. Open the toolbox by clicking on the Toolbox button on the toolbar. The toolbox is opened.
2. Click the text box tool on the toolbox.
3. Move the mouse pointer over the header or footer of a group and click within this section. The text box control appears in your report design.
4. Click within the text box. An I-beam appears within the box.
5. Type either =Sum(FieldName) or =Avg(FieldName) for summing or averaging the values in a field called FieldName. If the actual name of your field contains spaces, then enclose it in square brackets like so, =Sum([Field Name]).

NOTE: If you place this calculated field within the report header or footer, you get a grand total (or average for all records).

How do I save a report?

When you are happy with your report design, you must save the changes as follows:

1. Click the Save button on the toolbar, or choose Save from the File menu. If you haven't saved the report before, Microsoft Access prompts you for a name.
2. Type a name for the report and then choose OK. Microsoft Access saves the design of the report.

How do I know what my report looks like?

To see what your report looks like, use the print preview button on the toolbar. The technique is simply to click on one of your reports from the list in the main Database window and click the Print Preview button.

Use your scroll bars to navigate through the pages of your report.

My report still does not print correctly. Why?

Check the settings in your Print Setup. Choose Print Setup under the File menu and select your options. Otherwise, go to your report and set printing properties.

Can I create subreports?

Yes, you can create subreports within a report. The procedure is essentially the same as you would with forms/subforms. Click Microsoft Access Help button for further details.

Finally, if you have installed Microsoft Graph on your computer, you can also add graphs and charts to your Microsoft Access report or form. The procedure is essentially one of getting into Design View, choosing Chart from the Insert menu and following the directions in the Chart Wizard. Again ask Microsoft Access for details about this feature.