Count data by using a query

Applies to: Access for Microsoft 365, Access 2019, Access 2016, Access 2013, Access 2010, Access 2007.

This article articulates 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 even calculate average values, and acquire the smallest, largest, earliest, and latest values. Furthermore, Access supplies a feature known as the Total Row that you can deploy to count data in a datasheet without making any adjustments to your query’s design.

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 SumMinimumMaximum, 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, choose Query Design.
Access Ribbon Image
  1. In the Show Table dialogue box, double-click the table or tables that you want to use in your query, and then press Close. The specified table or tables display as windows in the upper section of the query designer. This figure depicts a typical table in the query designer:
A query with three fields in the design grid
  1. Double-click your preferred table fields to use in your query. You can enclose fields with descriptive data, such as names and descriptions, but you have to include the field that holds the values that you want to count. Every field shows up in a column in the query design grid.
  2. On the Design tab, in the Results group, select Run. The results of the query are presented in Datasheet View.
  3. Optionally, return to Design View and refine your query. To do so, right-click the document tab for the query and press Design View. You can then tweak the query as required by creating or deleting table fields. To erase a field, pick the column in the design grid and press DELETE.
  4. Optionally, save your query.

Add a Total row

  1. Enter 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 select Datasheet View. -or- For a .mdb file format database designed with an older version of Access, on the Home tab, in the Views group, press the arrow under View and choose 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, choose Totals.
Access Ribbon Image

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

  1. In the Total row, select your desired field to sum, and then pick Count from the list.

Hide a Total row

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

For further 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, press Query Design.
Access Ribbon Image
  1. In the Show Table dialogue box, double-click the table that you want to use in your query, and then choose Close. The table emerges in a window in the upper section of the query designer.
  2. Double-click your desired fields to use in the query, and ensure you add the field that you want to count. You can count fields of many data types, the exception being fields that includecomplex, repeating scalar data, such as a field of multivalued lists.
  3. On the Design tab, in the Show/Hide group, pick Totals. The Total row displays in the design grid and Group By shows in the row for each field in the query.
  4. In the Total row, select your preferred field to count and choose Count from the resulting list.
  5. On the Design tab, in the Results group, choose Run. The results of the query are displayed in Datasheet View.
  6. Optionally, save the query.

Count records in a group or category

  1. On the Create tab, in the Other group, select Query Design.
Access Ribbon Image
  1. In the Show Table dialogue box, double-click the table or tables that you want to use in your query, and then press Close. The table or tables display in a window in the upper section of the query designer.
  2. Double-click the field that has your category data, and also the field that contains the values that you want to count. Your query cannot have other descriptive fields.
  3. On the Design tab, in the Show/Hide group, select Totals. The Total row emerges in the design grid and Group By launches in the row for each field in the query.
  4. In the Total row, pick the field that you want to count and click Count from the resulting list.
  5. On the Design tab, in the Results group, select Run. The results of the query are illustrated in Datasheet View.
  6. Optionally, save the query.

Top of Page

Aggregate function reference

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

FunctionDescriptionUse with the data type(s)
SumAdds the items in a column. Works only on numeric and currency data.Number, Decimal, Currency
AverageCalculates 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
CountCounts 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.
MaximumReturns 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
MinimumReturns 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 DeviationMeasures 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
VarianceMeasures 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

Leave a Reply

%d bloggers like this: