Calculate a total or other aggregate values

In Design view

ShowCalculate a total or average on a form or report

  1. Open a form or report in Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.).
  2. Click the Text Box tool Button image on the toolbox (toolbox: A set of tools that is available in Design view for adding controls to a form or report.).
  3. Do one of the following:
  4. Select the text box, and then click Properties Button image on the toolbar.
  5. In the ControlSource property box, type an expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) that uses the Sum function to calculate a total or the Avg function to calculate an average.

ShowTip

ShowExample

Controls that calculate totals in the group footer and report footer

Callout 1 This control calculates the total sales for each order.

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

Notes

=Sum([Quantity] * [UnitPrice])

However, if you have a calculated field (calculated field: A field, defined in a query, that displays the result of an expression rather than displaying stored data. The value is recalculated each time a value in the expression changes.) in an underlying query (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form or report.), for example:

=ExtendedPrice: [Quantity] * [UnitPrice]

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

=Sum([ExtendedPrice])

ShowCalculate a percentage value on a report

  1. Open a report in Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.).
  2. Click the Text Box tool Button image on the toolbox (toolbox: A set of tools that is available in Design view for adding controls to a form or report.).
  3. Do one or both of the following:
  4. Do one or both of the following:

 Note   If your report includes multiple group levels (group level: The depth at which a group in a report or data access page is nested inside other groups. Groups are nested when a set of records is grouped by more than one field, expression, or group record source.), place the text box in the header or footer of the level for which you want Microsoft Access to calculate a percentage.

  1. Select the text box, and then click Properties Button image on the toolbar.
  2. 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.

ShowTip

ShowExample

Text boxes that calculate percentages shown in Design view

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

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

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

  1. 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.

ShowCalculate a total or other aggregate values on a data access page

Calculate an aggregate value

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

  1. Open the data access page in Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.).
  2. Select the text box or bound span control (bound span control: A control that is used on a data access page to bind HTML code to a Text or Memo field in an Access database or to a text, ntext, or varchar column in an Access project. You cannot edit the contents of a bound span control.) that contains the values you want to aggregate.
  3. On the toolbar, click the arrow next to the AutoSum Button image button, and then select the aggregate function that you want to use.

The results you see depend on the type of page and the group level to which the selected control belongs.

If the bound control belongs to Then
An ungrouped page, or the outermost group level on a grouped page (grouped data access page: A data access page that has two or more group levels.) Microsoft Access creates a new group level, and adds a bound span control to the footer section of the new group level.
An inner group level on a grouped page Access adds a bound span control to the footer section of the parent group level. If the footer section is not shown, the aggregate control is placed in the header section of the parent group level.

 Note   The list of aggregate functions that you see in the AutoSum list depends on the type of data you want to summarize. For numeric values, you can choose from Sum, Average, Min, Max, Count, StDev, and Any. For text, date, and time values, you can choose from Min, Max, Count, and Any.

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

This procedure does not apply to ungrouped pages. The page will not show the detail values of the field that is being used to calculate the aggregate values. If you want to show both individual and aggregate values, add the bound control to the page, and follow the previous procedure.

  1. Open the data access page in Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.).
  2. If the field list (field list: A window that lists all the fields in the underlying record source or database object, except in data access page Design view. In data access page Design view, it lists all the record sources and their fields in the underlying database.) is not displayed, click Field List on the View menu.
  3. In the field list, click the field that has the values you want to aggregate.
  4. Drag the field to the header or footer section where you want to show the aggregate values. For example, if the page has two group levels, Orders and Order Details, you can drag a field from the Order Details table to the header or footer section of the Orders group level.

The Layout Wizard dialog box appears.

  1. In the wizard dialog box, select the layout you want, and click OK.

Microsoft Access creates a bound span control in the target section. The control will use the Sum function for numeric values and the Count function for all other data types.

Calculate subtotals and grand totals

ShowCalculate 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.

In PivotTable view

ShowCalculate a total on a datasheet or form

  1. Open a datasheet or form in PivotTable view (PivotTable view: A view that summarizes and analyzes data in a datasheet or form. You can use different levels of detail or organize data by dragging the fields and items or by showing and hiding items in the drop-down lists for the fields.).
  2. Do one or more of the following:

ShowAdd a total field

ShowAdd a calculated total field

You can add a detail (Detail field: A field that displays all rows, or records, from the underlying record source.) or total field (total field: A field that summarizes data from the underlying record source. A total field can use a summary function, such as Sum or Count, or use an expression to calculate summary values.) that uses an expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) to calculate data.

  1. Open a datasheet or form in PivotTable view (PivotTable view: A view that summarizes and analyzes data in a datasheet or form. You can use different levels of detail or organize data by dragging the fields and items or by showing and hiding items in the drop-down lists for the fields.).
  2. On the PivotTable toolbar, click Calculated Totals and Fields Button image, 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.

ShowTip

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.
 
 
Applies to:
Access 2003