Calculate a total or other aggregate values

Calculate an aggregate value

When 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.
2. Select the text box or bound span control that contains the values you want to aggregate.
3. On the toolbar, click the arrow next to the AutoSum 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	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.

When 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.
2. If the field list 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.

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

Calculate a total based on an aggregate value

• Select the aggregate control, and then click AutoSum on the toolbar.

Microsoft Access creates a bound span control in the footer section of the next higher group level. If the aggregate control belonged to the highest group level, a new group level will be created. The new control will use the same aggregate function as the aggregate control on which it is based.

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.