ms-access Access SQL The COUNT() Function

Example

You can use the COUNT() function to return the number of records that match a query. The following 'Employee' table contains employee ID numbers and their associated manager's ID number.

Employee_ID	Manager_ID
12	37
22	37
37	63
42	45
45	63
57	45
59	45
63

A COUNT() statement can be used to find out how many employees have a specific manager:

SELECT COUNT(*) AS CNT FROM Employees WHERE Employee.Manager_ID = 37;

returns

CNT

2

The function can also be combined in more complicated queries. To find out how many employees are directly supervised by a specified person, the following can be applied:

SELECT T1.Employee_ID,
    (SELECT COUNT(*) AS CNT FROM Employees AS T2 WHERE T2.Manager_ID =
        T1.Employee_ID) AS Supervised_Count
FROM Employees AS T1;

returns:

Employee_ID	Supervised_Count
12	0
22	0
37	2
42	0
45	3
57	0
59	0
63	2

MSDN documentation may be found here.