Count the rows in a datasheet

This article explains how to use the Totals row to count the number of records (rows) in a datasheet. The Totals row is a new feature in Microsoft Office Access 2007 that simplifies the process of counting rows. In earlier versions of Access, you had to use a function such as Count or DCount in a query or an expression to count rows. Now, you can count them by running a command on the Ribbon, part of the Microsoft Office Fluent user interface.

The Totals row closely resembles the Totals row that you find in Microsoft Office Excel 2003 Lists or Microsoft Office Excel 2007 Tables. When you show a Totals row, you choose the Count function, or another common aggregate function such as Sum, Average, Min, or Max, from a drop-down list. For information about using the other aggregate functions provided by the Totals row, see the article Display column totals in a datasheet.

 Note    This article does not explain how to count records by using a query or an expression, or how to count records in a report. For more information about counting records in a report, see the article Create a grouped or summary report. For more information about using queries to sum and count data, see the article Sum data by using a query.

In this article


About using the Totals row

Counting the number of rows in a block of data is a common, and often critical, database task. For example, you may need to know how many sales a given broker made last month in addition to the total sales amount.

Office Access 2007 provides a new, easier way to count the items in a datasheet — the Totals row. In earlier versions of Access, you had to use a function such as Count or DCount in a query or an expression in order to count rows. The Totals row now automates that process. You can add a Totals row to any datasheet — a table, a query result set, or the data sheet in a split form. If you are new to Access, a datasheet grid is similar to an Excel worksheet.

 Note   You can also use the Totals row to quickly calculate other values such as averages, and to find the minimum or maximum value in a column of data. For more information about using the other functions in a Totals row, see the article Display column totals in a datasheet.

The following sections explain how to use the Totals row to count the items in a given column in a datasheet.

Top of Page Top of Page

Add a Totals row to a datasheet

The steps in this section explain how to count rows in datasheets. In this case, a datasheet can be a table, a query result set, or the datasheet portion of a split form.

  1. Open the table or query in Datasheet view.
  2. On the Home tab, in the Records group, click Totals.

Access Ribbon Image

The Totals row appears beneath the asterisk (*) row. This figure depicts a blank Totals row.

A blank Totals row

Notice that the down arrow sits on the opposite side of the cell. The button resides there to avoid disrupting the alignment of your text and numbers.

  1. In the Totals row, click the down arrow in the column that you want to count, and then select Count from the list.

Access counts the number of items (rows) in the column and displays the results.

 Note   For information about using the other functions in the Totals row, see the article Display column totals in a datasheet.

Top of Page Top of Page

Hide a Totals row

You never remove a Totals row from a datasheet. Instead, you hide the row. When you display the row again, Access remembers the function that you applied to each column in your datasheet and the row appears in its previous state.

  1. Open the table or query in Datasheet view.
  2. On the Home tab, in the Records group, click Totals.

Access Ribbon Image

Access removes the Totals row.

Top of Page Top of Page

 
 
Applies to:
Access 2007