Adding Calculations to a Report

Reports are often used just to display data. For example, it might be enough that an inventory report displays the in-stock, reorder level and on-order values for all a company's products. But anyone who uses a report as part of a decision-making process probably wants more than mere data. Such a person likely also needs to analyze the data in some way, and most data analysis requires one or more calculations. What were the total sales last quarter? How many days overdue are the unpaid invoices? How many records are in this report?

To answer these and many other questions within a report, you need to add one or more calculations. In the report Design view, you add calculations by adding text boxes, which you can use as unbound controls that display calculated results.

Here are the steps to follow to create a calculated text box control:

1.	Click the Text Box button in the Toolbox.
2.	Draw the text box on the form. Access adds the text box and an associated label.
3.	Click the text box to select it.
4.	Choose View, Properties (or press Alt+Enter) to display the control's property sheet.
5.	Choose the Data tab.
6.	Enter the expression in the Control Source property; be sure to begin the expression with an equal sign (=). (If you want to use the Expression Builder, click the ellipsis (...) button beside the Control Source property.)
7.	Close the property sheet.

A Faster Way

You can also enter the expression directly into the text box.

Calculations in the Headers and Footers

If you enter a calculation in the report header or report footer, Access performs the calculation over the entire report. Similarly, if the calculation is in the page header or page footer, Access uses only those records included in the page. If the calculation is in the group header or group footer, Access applies the expression to only those records included in the group.

For example, the report in Figure 4.37 contains a number of calculated text box controls.

Figure 4.37. To create a calculated control, enter an expression into an unbound text box (or into its Control Source property).

Two of the controls deal with the report grouping. The first, in the group header section (named UnitPrice Header in the figure), uses the following expression:

 ="Unit Prices from " & Min(FormatCurrency([UnitPrice])) & " to " & Max(FormatCurrency([UnitPrice])) 

Because this control resides in the group header, the expression applies only to those records in each grouping. In this case, the expression uses the Min and Max functions to specify the range of values within each group.

The second grouping-related calculated text box control in Figure 4.37 is in the group footer section (named UnitPrice Footer in the figure); it uses the following expression:

 ="Total Number of Records in Group: " & Count([UnitPrice]) 

This expression uses the Count function to display the number of records in the group.

The report in Figure 4.37 also includes two calculated text box controls in the Page Footer section. The text box on the left displays the current date and time using the following expression:

 =Now() 

On the right of the page footer, a text box displays page number data using the following expression:

 ="Page " & [Page] & " of " & [Pages] 

Here the [Page] identifier displays the current page number, and the [Pages] identifier displays the total number of pages in the report.

Figure 4.38 shows a preview of the report.

Figure 4.38. The preview of the form shown in Figure 4.37.