Count data by using a query

This article explains how to count the data returned by a query. For example, on a form or report, you can count the number of items in one or more table fields or controls. You can also calculate average values, and find the smallest, largest, earliest, and latest values. In addition, Access provides a feature called the Total Row that you can use to count data in a datasheet without having to alter the design of your query.

What do you want to do?

• Understand ways to count data

• Count data by using a Total row

• Count data by using a totals query

• Aggregate function reference

Understand ways to count data

You can count the number of items in a field (a column of values) by using the Count function. The Count function belongs to a set of functions called aggregate functions. You use aggregate functions to perform a calculation on a column of data and return a single value. Access provides a number of aggregate functions in addition to Count, such as:

• Sum, for summing a column of numbers.

• Average, for averaging a column of numbers.

• Maximum, for finding the highest value in a field.

• Minimum, for finding the lowest value in a field.

• Standard Deviation , for measuring how widely values are dispersed from an average value (a mean).

• Variance, for measuring the statistical variance of all values in the column.

Access provides two ways to add Count and other aggregate functions to a query. You can:

• Open your query in Datasheet view and add a Total row. The Total Row allows you to use an aggregate function in one or more columns of a query result set without having to change the design of your query.

• Create a totals query. A totals query calculates subtotals across groups of records. For example, if you want to subtotal all sales by city or by quarter, you use a totals query to group your records by the category you want and then sum the sales figures. In contrast, a Total row calculates grand totals for one or more columns (fields) of data.

Note: The how-to sections in this document emphasize using the Count function, but remember that you can use other aggregate functions in your Total rows and queries. For more information about using the other aggregate functions, see Aggregate function reference later in this article.

For more information about ways to use the other aggregate functions, see the article Display column totals in a datasheet.

The steps in the following sections explain how to add a Total row and how to use a totals query to count data. As you proceed, remember that the Count function works on a larger number of data types than do the other aggregate functions. For example, you can run a Count function against any type of field except one containing complex, repeating scalar data, such as a field of multivalued lists.

In contrast, many of the aggregate functions work only on data in fields set to a specific data type. For example, the Sum function works only with fields set to the Number, Decimal, or Currency data types. For more information about the data types that each function requires, see Aggregate function reference, later in this article.

For general information about data types, see the article Modify or change the data type set for a field.

Top of Page

Count data by using a Total row

You add a Total row to a query by opening your query in Datasheet view, adding the row, and then selecting the Count function or another aggregate function, such as Sum, Minimum, Maximum, or Average. The steps in this section explain how to create a basic select query and add a Total row.

Create a basic select query

1. On the Create tab, in the Other group, click Query Design.

   

2. Double-click the table or tables that you want to use in your query, and then click Close.

   The selected table or tables appear as windows in the upper section of the query designer. This figure shows a typical table in the query designer:

   

3. Double-click the table fields that you want to use in your query.

   You can include fields that contain descriptive data, such as names and descriptions, but you must include the field that contains the values that you want to count.

   Each field appears in a column in the query design grid.

4. On the Design tab, in the Results group, click Run.

   The results of the query are displayed in Datasheet view.

5. Optionally, go back to Design view and adjust your query. To do so, right-click the document tab for the query and click Design View. You can then adjust the query as needed by adding or removing table fields. To remove a field, select the column in the design grid and press DELETE.

6. Optionally, save your query.

Add a Total row

1. Open your query in Datasheet view. To do so for a database in the .accdb file format, right-click the document tab for the query and click Datasheet View.

   -or-

   For a .mdb file format database created with an older version of Access, on the Home tab, in the Views group, click the arrow below View and click Datasheet View.

   -or-

   In the Navigation Pane, double-click the query. Doing this runs the query and loads the results into a datasheet.

2. On the Home tab, in the Records group, click Totals.

   

   A new Total row appears below the last row of data in your datasheet.

3. In the Total row, click the field that you want to sum, and then select Count from the list.

Hide a Total row

• On the Home tab, in the Records group, click Totals.

For more information about using a Total row, see the article Display column totals in a datasheet.

Top of Page

Count data by using a totals query

You count data by using a totals query instead of a Total row when you need to count some or all of the records returned by a query. For example, you can count the number of sales transactions, or the number of transactions in a single city.

Typically, you use a totals query instead of a Total row when you need to use the resulting value in another part of your database, such as a report.

Count all the records in a query

1. On the Create tab, in the Other group, click Query Design.

   

2. Double-click the table that you want to use in your query, and then click Close.

   The table appears in a window in the upper section of the query designer.

3. Double-click the fields that you want to use in the query, and make sure you include the field that you want to count. You can count fields of most data types, the exception being fields that contain complex, repeating scalar data, such as a field of multivalued lists.

4. On the Design tab, in the Show/Hide group, click Totals.

   The Total row appears in the design grid and Group By appears in the row for each field in the query.

5. In the Total row, click the field that you want to count and select Count from the resulting list.

6. On the Design tab, in the Results group, click Run.

   The results of the query are displayed in Datasheet view.

7. Optionally, save the query.

Count records in a group or category

1. On the Create tab, in the Other group, click Query Design.

   

2. Double-click the table or tables that you want to use in your query, and then click Close.

   The table or tables appear in a window in the upper section of the query designer.

3. Double-click the field that contains your category data, and also the field that contains the values that you want to count. Your query cannot contain other descriptive fields.

4. On the Design tab, in the Show/Hide group, click Totals.

   The Total row appears in the design grid and Group By appears in the row for each field in the query.

5. In the Total row, click the field that you want to count and select Count from the resulting list.

6. On the Design tab, in the Results group, click Run.

   The results of the query are displayed in Datasheet view.

7. Optionally, save the query.

Top of Page

Aggregate function reference

The following table lists and describes the aggregate functions that Access provides for use in the Total row and in queries. Remember that Access provides more aggregate functions for queries than it does for the Total row. Also, if you work with an Access project (an Access front end connected to a Microsoft SQL Server database), you can use the larger set of aggregate functions that SQL Server provides. For more information about that set of functions, see Microsoft SQL Server Books Online.

Function	Description	Use with the data type(s)
Sum	Adds the items in a column. Works only on numeric and currency data.	Number, Decimal, Currency
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
Count	Counts the number of items in a column.	All data types except those containing 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 Create or delete a multivalued field.
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
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
Standard Deviation	Measures how widely the values are dispersed from an average value (a mean). For more information about using this function, see the article Display column totals in a datasheet.	Number, Decimal, Currency
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 article Display column totals in a datasheet.	Number, Decimal, Currency

Top of Page