Display column totals in a datasheet

Microsoft Office Access 2007 provides a new tool called the Totals row that simplifies the process of summing a column of data. You can also use the Totals row for other calculations such as averages, counting the number of items in a column, and finding the minimum or maximum value in a column of data.

The Totals row makes it easier and faster to use a set of aggregate functions — functions that calculate values across a range of data. In previous versions of Access, you had to use those functions in queries or in Visual Basic for Applications (VBA) code. You can still use those methods, but the Totals row gives you a way to use an aggregate function quickly. This article introduces the aggregate functions and explains how to use them in a Totals row.

 Tip   Try Office 2010 Access 2010 includes new tools that help you enter expressions more quickly and accurately. Read an article or try Office 2010.

What do you want to do?


Understand Sum and the other aggregate functions

Aggregate functions perform calculations on columns of data and return a single result. You use aggregate functions when you need to calculate a single value, such as a sum or an average. As you proceed, remember that you use aggregate functions with columns of data. That may seem like an obvious statement, but when you design and use a database, you tend to focus on rows of data and individual records — you make sure that users can enter data in a field, move the cursor to the right or left and fill in the next field, and so on. In contrast, you use aggregate functions to focus on groups of records in columns.

For example, suppose you use Access to store and track sales data. You can use aggregate functions to count the number of products sold in one column, calculate total sales amounts in a second column, and calculate the average sales amount for each product in a third column.

This table lists and describes the aggregate functions that Access provides in the Totals row. Remember that Access provides additional aggregate functions, but you must use them in queries. Also, if you work with an Access project (an Access front end connected to a Microsoft SQL Server database), you can use a larger set of aggregate functions. For more information about those additional functions, see the online Help for SQL Server.

Function Description Use with the data type(s) Available in Totals row?
Average Calculates the average value for a column. The column must contain numeric, currency, or date/time data. The function ignores null values. Number, Decimal, Currency, Date/Time Yes
Count Counts the number of items in a column. All data types except complex repeating scalar data, such as a column of multivalued lists. For more information about multivalued lists, see the articles Guide to multivalued fields and Add or change a lookup field that lets you store multiple values. Yes
Maximum Returns the item with the highest value. For text data, the highest value is the last alphabetic value; and Access ignores case. The function ignores null values. Number, Decimal, Currency, Date/Time Yes
Minimum Returns the item with the lowest value. For text data, the lowest value is the first alphabetic value; and Access ignores case. The function ignores null values. Number, Decimal, Currency, Date/Time Yes
Standard Deviation Measures how widely values are dispersed from an average value (a mean). For more information, see More about the Standard Deviation and Variance functions, later in this article. Number, Decimal, Currency Yes
Sum Adds the items in a column. Works only on numeric and currency data. Number, Decimal, Currency Yes
Variance Measures the statistical variance of all values in the column. You can use this function only on numeric and currency data. If the table contains less than two rows, Access returns a null value. For more information about variance functions, see the section More about the Standard Deviation and Variance functions, later in this article. Number, Decimal, Currency Yes

The following section explains how to use each function in a Totals row.

Top of Page Top of Page

Sum values by using the Totals row

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

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

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

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

  1. Open the target file into which you want to paste your data.
  2. Select the last row of your destination table, query result set, or form.
  3. 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

  1. Open a table, query result set, or form. You can use any view except Design view.
  2. 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.

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

Top of Page Top of Page

More about the Standard Deviation and Variance functions

The Standard Deviation and Variance functions compute statistical values. Specifically, they calculate where values are clustered around their mean (their average) in a standard distribution (a bell curve).

For example, suppose you randomly collect 10 tools stamped from the same machine and measure them for breaking strength — a process that tests the machine and your quality control measures. If you compute an average breaking strength, you can see that most of the tools have a breaking strength that is close to that average, while a few have a greater strength and a few have a lesser strength. However, if you only compute the average breaking strength, that value does not tell you how well your quality control process works, because a few exceptionally strong or weak tools can skew the average up or down.

The Variance and Standard Deviation functions work around that problem by telling you how close your values are to the mean. For breaking strength, the smaller the numbers returned by either function indicate that your manufacturing processes are working well, because few of the tools have a breaking strength above or below the average.

A complete discussion of variance and standard deviation is beyond the scope of this article. You can consult any number of statistics Web sites for more information about both topics. Remember these rules as you use the Variance and Standard Deviation functions:

  • The functions ignore null values.
  • The Variance function uses this formula:
    Formula
  • The Standard Deviation function uses this formula:
    Formula

Top of Page Top of Page

 
 
Applies to:
Access 2007