>
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 Shipped Date 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
SQL Aggregate Functions, 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 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. You can use the Count(*) function 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 DISTINCTROW Count(*) AS CountOfOrders, Orders.ShipCity
FROM Orders
GROUP BY Orders.ShipCity;
You can achieve the same results by dragging the OrderID field to the query design grid, and choosing Count in the Total cell beneath it. This query will be slightly slower than the one that uses the Count(*) function. Note the differences between the following SQL statements.
SELECT DISTINCTROW Count(Orders.OrderID)
AS CountOfOrderID, Orders.ShipCity
FROM Orders
GROUP BY Orders.ShipCity;
You can also use an SQL statement such as this one within Visual Basic code. For example, using the following code you can create a Recordset object based on the query defined by this SQL statement.
Dim dbs As DatabAse, rst As Recordset, strSQL As String
Set dbs = CurrentDb
strSQL = "SELECT DISTINCTROW Count(*) AS CountOfOrders, Orders.ShipCity FROM Orders GROUP BY Orders.ShipCity;"
Set rst = dbs.OpenRecordset(strSQL)
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.
SELECT Count(ShipCountry)AS [UK Orders] FROM Orders
WHERE ShipCountry = 'UK';
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])