Count Function

Description

Calculates the number of records returned by a query.

Syntax

Count(expr)

The expr placeholder represents a string expression identifying the field that contains the data you want to count or an expression that performs a calculation using the data in the field. Operands in expr can include the name of a table field or function (which can be either intrinsic or user-defined but not other SQL aggregate functions). You can count any kind of data, including text.

Remarks

You can use Count to count the number of records in an underlying query. For example, you could use Count to count the number of orders shipped to a particular country.

Although expr can perform a calculation on a field, Count simply tallies the number of records. It doesn't matter what values are stored in the records.

The Count function doesn't count records that have Null fields unless expr is the asterisk (*) wildcard character. If you use an asterisk, Count calculates the total number of records, including those that contain Null fields. Count(*) is considerably faster than Count([Column Name]). Don't enclose the asterisk in quotation marks (' '). The following example calculates the number of records in the Orders table:

SELECT Count(*)
AS TotalOrders FROM Orders;

If expr identifies multiple fields, the Count function counts a record only if at least one of the fields is not Null. If all of the specified fields are Null, the record isn't counted. Separate the field names with an ampersand (&). The following example shows how you can limit the count to records in which either ShippedDate or Freight isn't Null:

SELECT
Count('ShippedDate & Freight')
AS [Not Null] FROM Orders;

You can use Count in a query expression. You can also use this expression in the SQL property of a QueryDef object or when creating a Recordset object based on an SQL query.

See Also QueryDef object ("DAO Language Reference"), RecordCount property ("DAO Language Reference"), SELECT statement, SQL property ("DAO Language Reference"), Sum function.

Specifics (Microsoft Access)

In Microsoft Access, you can use the Count function in the query design grid, in an SQL statement in SQL view of the Query window, or in an SQL statement within Visual Basic code. You can also use the Count function in a calculated control on a form or report.

The Count function is most useful in totals queries and crosstab queries. It functions the same way whether you create the query in the query design grid or as an SQL statement in SQL view.

In the query design grid, you can create a new totals query by clicking the Totals button on the Query Design toolbar. The Total row is then inserted in the grid. You can set the Total cell beneath a field to the aggregate function to perform on the data in that field.

The fastest way to count all the records in a query is to use the Count(*) function, which you can use in a calculated field in a query.

For example, suppose you have an Orders table that has both an OrderID field and a ShipCity field. You can create a query that displays the number of orders sent to each city. Create a new totals query, and drag the ShipCity field to the query design grid. Set the Total cell beneath the ShipCity field to Group By.

Next, create a calculated field by typing the following expression into a new Field cell:

CountOfOrders: Count(*)

Then, set the Total cell beneath this field to Expression. When you run the query, it will display the number of orders sent to each city.

You can view the SQL statement for this query by switching to SQL view. In this example, Microsoft Access creates the following SQL statement:

SELECT ShipCity, Count(*) AS CountOfOrders
FROM Orders GROUP BY ShipCity;

You can achieve the same results by dragging the OrderID field to the query design grid, and clicking Count in the Total cell beneath it. This query will be slightly slower than the one that uses the Count(*) function. Note how the following differs from the preceding SQL statement:

SELECT ShipCity, Count(OrderID) AS CountOfOrders FROM Orders GROUP BY ShipCity;

You can also use an SQL statement within Visual Basic code. For example, the following procedure creates a dynaset-type Recordset object from the faster of the two preceding SQL statements:

Sub CountOrders()
    Dim dbs As Database, rst As Recordset, strSQL As String
    Set dbs = CurrentDb
    strSQL = "SELECT ShipCity, Count(*) AS CountOfOrders " _
        & "FROM Orders GROUP BY ShipCity;"
    Set rst = dbs.OpenRecordset(strSQL)
    rst.MoveLast
    Debug.Print rst.RecordCount
    Set dbs = Nothing
End Sub

To use the Count function in a calculated control, set the control's ControlSource property to an expression containing the Count function. For example, to display the number of orders in a set of orders in a text box, enter the following expression in the ControlSource property of the text box.

=Count([OrderID])

If you use the Count function in a calculated control, you can restrict the set of records against which the function is performed by setting the form's Filter property.

Example

This example uses the Orders table to calculate the number of orders shipped to the United Kingdom.

This example calls the EnumFields procedure, which you can find in the SELECT statement example.

Sub CountX()

    Dim dbs As Database, rst As Recordset

    ' Modify this line to include the path to Northwind
    ' on your computer.
    Set dbs = OpenDatabase("Northwind.mdb")
    
    ' Calculate the number of orders shipped
    ' to the United Kingdom.
    Set rst = dbs.OpenRecordset("SELECT" _
        & " Count (ShipCountry)" _
        & " AS [UK Orders] FROM Orders" _
        & " WHERE ShipCountry = 'UK';")
    
    ' Populate the Recordset.
    rst.MoveLast
    
    ' Call EnumFields to print the contents of the
    ' Recordset. Pass the Recordset object and desired
    ' field width.
    EnumFields rst, 25

    dbs.Close

End Sub

Example (Microsoft Access)

The following example assumes that you have an Orders table that contains a field called ShipCountry. You can use the Count function to calculate the number of orders shipped to the United Kingdom. Enter the following expression in SQL view in the Query window:

SELECT Count([ShipCountry]) AS [UKOrders] FROM Orders WHERE [ShipCountry] = 'UK';

The next example creates a calculated control that displays the number of orders in the same Orders table. Open a new form and set its RecordSource property to Orders. Enter the following expression in the ControlSource property of a text box on the form. To apply a condition that limits the count to only some records, such as those for orders shipped to the United Kingdom, set the form's Filter property.

=Count([ShipCountry])