How to add a column via a query which counts the total rows with a specific criteria in a table with circular relationship in MS ACCESS 2007
- by Xaqron
I have a simple table "Employees" with this fields:
ID, ParentID, Name
ParentID is Nullable since an employee may have no Manager.
This table has a one-to-many relationship with itself:
ID --one--to--many--> ParentID
Now I want a query which returns this columns:
Name, Count of rows where their ParentID equals to the current row ID (the row is the manager of that rows)
Sample Table:
ID | ParentID | Name
======================
1 | 0 | John
----------------------
2 | 1 | Bob
----------------------
3 | 1 | Alice
----------------------
4 | 3 | Jack
This way I can find an employee is the manager of how many other employees.
The result should be something like this:
Name | Count of Employees
==========================
John | 2
--------------
Bob | 0
--------------
Alice | 1
--------------
Jack | 0
How can I achieve this in MS ACCESS 2007?
* I have tried built-in query builder without any success.
