databasedev.co.uk - database solutions and downloads for microsoft access

Dashboard Builder for Microsoft Access
Need to see key data from your Access database in a single dashboard? Tired of creating query after query to really understand your Access data? Dashboard Builder for Microsoft Access is an easy to use tool to create powerful dashboards in minutes…without being a SQL wizard. Free trial available

SQL Count in Microsoft Access

SQL Count Function:

Using SQL Count will allow you to determine the number of rows, or non-NULL values, in your chosen result set. When we want to count the entire number of rows in the database table, we can use COUNT (*)

If we define a column in the COUNT statement: COUNT ([column_name]), we count the number of rows with non-NULL values in that column.

We can specify to count only unique values by adding the DISTINCT keyword to the statement.

SQL COUNT Syntax:

COUNT([ALL | DISTINCT] expression)

The Count function does not count records that have Null fields unless expression 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]).

If we use the following example table, we can demonstrate the use of the COUNT function.

tblEmployees
ID LastName FirstName Department Salary
01 Smith Janet 10 20000
02 Walters Ed 10 30000
03 McGee Keith 20 28000
04 <NULL> Alice 30 29000
05 Smith Brenda 30 32000

If we want to find the number of Employee entries in our database table we use the following:

SELECT COUNT(*)
FROM tblEmployees

This will return the result of: 5 records.

If we use the Count([Column Name]), and use the LastName as the column, we will return a different result, due to the Null value contained in the column. Our syntax for this would be:

SELECT COUNT([LastName)
FROM tblEmployees

This will return the result of: 4 records.

We can also use COUNT and DISTINCT to return the number of distinct entries in the table. For example, if we want to find out the number of distinct departments, we would use the following syntax:

SELECT COUNT(DISTINCT column(s))
FROM table

So, to count the distinct departments, our query would look like:

SELECT COUNT(DISTINCT [Department])
FROM tblEmployees

This will return the result of: 3 records (Distinct Departments consist of Department 10, 20 and 30).