If you need to sum a column of data or use another aggregate function quickly, you can add a Totals row to a table, query result set, or a split form — a form that contains a datasheet. Totals rows save a lot of time, because you do not have to add a function to a query or write VBA code.
Remember these rules as you proceed:
The functions provided by the Totals row vary with the data type setting for the column of data. For example, Access provides only the Count function for fields set to the Text data type because you cannot perform mathematical operations on text data.
The first field in a Totals row contains the word Total until you change it by selecting a function.
You cannot add a Totals row to a report. You can use aggregate functions in reports, but you need to use other techniques. For more information about aggregate functions and expressions in reports, see the article Create a grouped or summary report.
You can add a Totals row to tables and queries open in Datasheet view. You can also add a Totals row to a split form open in Form views.
By default, Access excludes Totals rows from filter and sort operations.
The following sections explain how to use a Totals row in a datasheet and a split form.
Add a Totals row
Open a table or query in Datasheet view, or open a split form in Form view. To do so, in the Navigation Pane, right-click the table, query, or form and click Design View or Form View on the shortcut menu.
On the Home tab, in the Records group, click Totals.
Access Ribbon Image
A new Total row appears below the star row in your datasheet or split form.
A new, blank Totals row
Note If you are working in a split form that was created in an earlier version of Access and then converted to the new .accdb format, you do not see a star row in the datasheet. That difference does not affect a Totals row in any way.
In the Total row, click the field that you want to sum, and then select SUM from the list.
Selecting the SUM function for a Totals row
For information about the function, see the section Understand the aggregate functions, earlier in this article.
Remove a Totals row
On the Home tab, in the Records group, click Totals.
Access Ribbon Image
Note You cannot cut or delete a Totals row, you can only turn it on or off. However, you can copy a Totals row and paste it into another file, such as a Microsoft Office Excel 2007 workbook or a Microsoft Office Word 2007 document. The next set of steps explains how to copy a Totals row.
Copy a Totals row to another file
Click the row selector — the box at the right or left end of the Total row — to highlight the entire row.
On the Home tab, in the Clipboard group, click Copy.
Access Ribbon Image
–or–
Press CTRL+C.
Open the target file into which you want to paste your data.
Select the last row of your destination table, query result set, or form.
On the Home tab, in the Clipboard group, click Paste.
Access Ribbon Image
Top of Page Top of Page
Count the number of values in a column
You use the Count function when you need to count the number of items in a column. The Count function runs only against a single column of data and returns a single value. The function works with all data types.
Count items in a column
Open a table, query result set, or form. You can use any view except Design view.
On the Home tab, in the Records group, click Totals.
Access Ribbon Image
A new Totals row appears below the star row in your datasheet.
In the Total row, click the field that you want to count and select COUNT from the list. For information about the function, see the section Understand Sum and the other aggregate functions, earlier in this article.
Note Access does not calculate the value for the function until you commit your changes. To do so, you can enter a new value in another field, or press F9 to requery the data.
