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
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.
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.
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).