Calculate a total or other aggregate values

1. Open a form or report in Design view.
2. Click the Text Box tool on the toolbox.
3. Do one of the following:
   • To calculate a total or average for a group of records, add the text box to the group header or footer.
   • To calculate a grand total or average for all records in a form or report, add the text box to the header or footer of the form or report.
4. Select the text box, and then click Properties on the toolbar.
5. In the ControlSource property box, type an expression that uses the Sum function to calculate a total or the Avg function to calculate an average.

   Tip

   In a Microsoft Access database (.mdb), for a form or report, you can click the Build button to use the Expression Builder to create the expression.

   Example

   

   This control calculates the total sales for each order.

   This control calculates the total sales for all orders in the report.

Notes

• The Employee Sales by Country report in the Northwind sample database has an example of group totals and a grand total. To view this report, point to Sample Databases on the Help menu, click either Northwind Sample Database or Northwind Sample Access Project, and then open the Employee Sales by Country report in Design view.

• When computing a total in a form or report in Design view with an aggregate function such as Sum or a domain aggregate function such as DSum, you can't use the name of a calculated control in the Sum function. You must repeat the expression in the calculated control. For example:

  =Sum([Quantity] * [UnitPrice])

  However, if you have a calculated field in an underlying query, for example:

  =ExtendedPrice: [Quantity] * [UnitPrice]

  you can use the name of that field in the Sum function, as shown here:

  =Sum([ExtendedPrice])

1. Open a report in Design view.
2. Click the Text Box tool on the toolbox.
3. Do one or both of the following:
   • If the report does not calculate group totals, click the group footer to add a text box to display the group totals.
   • If the report does not calculate a grand total, add a text box to the report footer to display the grand total.
4. Do one or both of the following:

   • To calculate what percentage of a group total or the grand total each item is, add a text box to the detail section.

   • To calculate what percentage of the grand total a group of items is, add a text box to the group header or footer.

   Note  If your report includes multiple group levels, place the text box in the header or footer of the level for which you want Microsoft Access to calculate a percentage.

5. Select the text box, and then click Properties on the toolbar.
6. In the ControlSource property box, type an expression that divides the smaller total by the larger total of which the smaller total is a part.

   Tip

   In a Microsoft Access database (.mdb), you can click the Build button to use the Expression Builder to create the expression.

   Example

   

   The percentage of the grand total is calculated by ...

   ... dividing the value of the DailyTotal control in the ShippedDate footer ...

   ... by the value of the GrandTotal control in the report footer.

7. Set the Format property of the text box to Percent.

Note   The Employee Sales by Country report in the Northwind sample database has an example of percentages. To view this report, point to Sample Databases on the Help menu, click either Northwind Sample Database or Northwind Sample Access Project, and then open the Employee Sales by Country report in Design view.

Calculate an aggregate value

When the control you want to use to calculate the aggregate value is already on the page

When the control you want to use to calculate the aggregate value is not on the page

Calculate subtotals and grand totals

Calculate a total based on an aggregate value

Notes

• You can set the properties of the aggregate control, and move, resize, or format the control to make it look the way you want.
• You can change the control's aggregate function by setting the control's TotalType property to the aggregate function you want. When you change the aggregate function, the default alias and caption of the control change to reflect the underlying aggregate function. To prevent the control name from changing, type a new alias for the control in its ControlSource property. User-specified aliases don't change when the aggregate function is changed.
• Changing the aggregate function of a control does not affect other aggregate controls, such as those that calculate subtotals or grand totals.

1. Open a datasheet or form in PivotTable view.

2. Do one or more of the following:

   Add a total field

   You can add total fields to the detail area, but not to the row, column, or filter area.

   1. Open a datasheet or form in PivotTable view.

   2. Click the field that contains the values you want to summarize.

   3. On the PivotTable toolbar, click AutoCalc , and then click the summary function you want.

   Add a calculated total field

   You can add a detail or total field that uses an expression to calculate data.

   1. Open a datasheet or form in PivotTable view.

   2. On the PivotTable toolbar, click Calculated Totals and Fields , and then do one of the following:

      • To create a detail field, click Create Calculated Detail Field.

      • To create a total field, click Create Calculated Total.

   3. In the Properties dialog box, type the expression you want on the Calculation tab, and then click Change.

      Tip

      To add field references, select a field that you want to use from the Insert Reference To box, and then click the Insert Reference To button.

   Notes

   • To edit the expression that is being used in a calculated field, select the field, edit the expression, and then click Change on the Calculation tab of the Properties dialog box. The field values will be automatically recalculated using the new expression.

   • By default, total fields that summarize numeric data use the Sum summary function. Fields that summarize other types of data use the Count summary function.