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
Posted
by
Xaqron
on Stack Overflow
See other posts from Stack Overflow
or by Xaqron
Published on 2012-08-29T21:12:58Z
Indexed on
2012/08/29
21:38 UTC
Read the original article
Hit count: 359
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.
© Stack Overflow or respective owner
