MS Access

Creating Calculated Controls

Footers are most often used to summarize report information. For example, a Group Footer could total the number of harassing phone calls each telemarketer made and the Report Footer could calculate the number of harassing phone calls made by all telemarketers.

Example of a calculated control used to total the Totals field.

Calculated controls usually appear in the footer sections of a report.

If you create a report using the (the preferred method), you will specify which fields you want to summarize and the calculation you want to use to summarize them. Of course, you can always add your calculated control or calculated field to summarize information on an existing report. Figure shows an example of a calculated control. A calculated control displays totals and other arithmetic computations on a form or report. Table describes the functions you will use most often to summarize your reports.

  1. Make sure you have the modified rptEmployeeSales report you worked on in the previous lesson open in Design view.

    The section footer in which you place a calculated control is very important, as each footer section calculates/summarizes records differently:

    • Group Footer: Calculates all the records in a group.

    • Page Footer: Calculates all the records on the page.

    • Report Footer: Calculates all the records in the report.

    You want to add a calculated control to the Report Footer that will calculate the total sales for all records. First though, you will need to resize the Report Footer so that there is enough room to add such a control. Here's how to change the size of a section.

  2. Click and drag the Report Footer section divider down a half-inch.

    Now that you have enough room in the Report Footer you can add the calculated control. There are two ways to add a calculated control to a form or report:

    • Click the Text Box control on the Toolbox and click and drag where you want to add the control.

    • Copy an existing text box control, select the desired location, and paste the copied text box control.

    You can use either method, but the copy and paste method is a little faster and easier because it copies formatting options and gives the new control a consistent look.

  3. Select the Total text box control in the Detail section and click the Copy button on the toolbar. Click the Report Footer section divider and click the Paste button on the toolbar.

    Access pastes the copied control. Next you have to add an expression to the control.

  4. With the new Total text box in the Report Footer still selected, click the Properties button on the toolbar.

    The Properties dialog box appears. You need to enter the expression in the Control Source property, which you can find on the Data tab.

  5. Click the Data tab, click in the Control Source box, type =SUM([Total]), and close the Properties dialog box.

    You're finished adding a calculated control that will total the Total fields on the report.

    Next you want to add another footer and calculated control that will total the total sales by employee. First you need to add an Employee Group Footer section to your report.

  6. Click the Sorting and Grouping button on the toolbar.

    Another way to sort and group is to select View » Sorting and Grouping from the menu.

    The Sorting and Grouping dialog box appears.

  7. Click the Employee field, click the Group Footer box in the Group Properties section, click the list arrow, select Yes from the list, and then close the Sorting and Grouping dialog box.

    Because the Total text box control you created in Steps 3-5 already contains the SUM expression you need, you can simply copy and paste the control in the Group Footer.

  8. Copy the Total text box control in the Report Footer section, click the Employee Footer section divider, and paste the control.

    You need to add some meaningful labels to your report. Often, it's useful to add a calculated control that mixes some text with the current value of a field to produce an informative notice for the report.

  9. Copy the Total text box control in the Employee Footer section and paste it in the same section. Change the Data Source property of the pasted control to ="Total for " & [Employee].

    This expression will display the text "Total for" and the employee's name. Let's finish tidying up the report.

  10. Add a Grand Totals: text label to the Report Footer section, then click and drag the three new calculated fields and one text label so that your report looks like the one in figure.

    Let's see how our report looks.

  11. Click the View button to switch to Print Preview. Scroll down and notice the calculated controls. When you're finished, save your changes and close the report.

Common Summary Functions

Sum

Totals all the values listed in a field. e.g. Sum([InvoiceTotal])

Maximum

Finds and displays the largest value listed in a field. e.g. Max([InvoiceTotal])

Minimum

Finds and displays the smallest value listed in a field. e.g. Min([InvoiceTotal])

Average

Calculates the average of all the values listed in a field. e.g. Avg([InvoiceTotal])

Count

Counts how many values are listed in a field. e.g. Count([InvoiceTotal])

To create a calculated control:

  1. display the report in design view.

  2. select the control and click the properties button on the toolbar.

    or...

    right-click the control and select properties from the shortcut menu.

    or...

    select the control and select view » properties from the menu.

  3. click the data tab and click in the control source box.

  4. type the expression in the control source (see table for some examples).

    or...

    click the build button and use the expression builder to create the expression, then click ok when you're finished.

  5. close the properties dialog box.