stoly
11-29-2010, 07:32 PM
I have one query in oracle, and want to make same in access, but its impossible to find solution.
The following hierarchical query uses the CONNECT BY clause to define the relationship between employees and managers, and uses the LEVEL pseudocolumn to show parent and child rows:
SELECT employee_id, last_name, manager_id, LEVEL
FROM employees
CONNECT BY PRIOR employee_id = manager_id;
EMPLOYEE_ID LAST_NAME MANAGER_ID LEVEL
----------- ------------------------- ---------- ----------
101...............Kochhar...........100...........1
108...............Greenberg........101...........2
109...............Faviet.............108...........3
110...............Chen...............108...........3
111...............Sciarra............108...........3
112...............Urman.............108...........3
113...............Popp...............108...........3
...
Is this possible in MS access 2003 or later version?
The following hierarchical query uses the CONNECT BY clause to define the relationship between employees and managers, and uses the LEVEL pseudocolumn to show parent and child rows:
SELECT employee_id, last_name, manager_id, LEVEL
FROM employees
CONNECT BY PRIOR employee_id = manager_id;
EMPLOYEE_ID LAST_NAME MANAGER_ID LEVEL
----------- ------------------------- ---------- ----------
101...............Kochhar...........100...........1
108...............Greenberg........101...........2
109...............Faviet.............108...........3
110...............Chen...............108...........3
111...............Sciarra............108...........3
112...............Urman.............108...........3
113...............Popp...............108...........3
...
Is this possible in MS access 2003 or later version?