Hierarchical queries!!!

[stoly]

I have one query in oracle, and want to make same in access, but its impossible to find solution (just for me)



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?

[RuralGuy]

That is called a Self Join.

[stoly]

That is called a Self Join.

Relations are a good thing, but I have infinitely many levels. This would mean that I have to make thousands of the same relations. Is there another way to do gets done.
I've never worked with Access, and this seems so simple, but I can not find a solution.

[RuralGuy]

I'm sorry but I cannot help beyond what I posted.

[weekend00]

Connect By is so powerful, I think you may need a procedure with loop/recurse to achieve that.

[stoly]

I'm sorry but I cannot help beyond what I posted.

Ok, thanks!

Connect By is so powerful, I think you may need a procedure with loop/recurse to achieve that.

And I thought about the access loop, however I am MSaccess total beginner so if you can even extra help around the loop.

[weekend00]

is the table in your first post the result that you want?

Create a table Hie_emp to hold the result:
Hie_emp
EMPLOYEE_ID number, LAST_NAME text, MANAGER_ID number, LEVEL number, ID autonumber

put following code in a module, run the sub Hierarchical, check table Hie_emp for the result (order by ID)

Public Sub Hierarchical()
   Dim root As Long
   root = DFirst("employee_ID", "employees", "manager_id=100")
   If root = Null Then Exit Sub
   'add a root node
   CurrentDb.Execute "delete * from hie_emp"
   CurrentDb.Execute "insert into Hie_emp (employee_id,last_name,Manager_id,[Level]) select employee_id,last_name,Manager_id,1 from employees where employee_id=" & root
   CreateTree root, 1
End Sub
Private Sub CreateTree(PID As Long, level As Long)
    Dim rst As DAO.Recordset
 
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM employees where manager_id=" & PID & " ORDER BY employee_id")
    Do While Not rst.EOF
       'adding a node for every one
        CurrentDb.Execute "insert into Hie_emp (employee_id,last_name,Manager_id,[Level]) values ( '" & rst!employee_id & "','" & rst!last_name & "'," & rst!Manager_id & "," & (level + 1) & ")"
        CreateTree rst!employee_id, level + 1
        rst.MoveNext
    Loop  'while not .eof
    rst.Close   'rst
End Sub

[stoly]

is the table in your first post the result that you want?

Create a table Hie_emp to hold the result:
Hie_emp
EMPLOYEE_ID number, LAST_NAME text, MANAGER_ID number, LEVEL number, ID autonumber

put following code in a module, run the sub Hierarchical, check table Hie_emp for the result (order by ID)

Public Sub Hierarchical()
   Dim root As Long
   root = DFirst("employee_ID", "employees", "manager_id=100")
   If root = Null Then Exit Sub
   'add a root node
   CurrentDb.Execute "delete * from hie_emp"
   CurrentDb.Execute "insert into Hie_emp (employee_id,last_name,Manager_id,[Level]) select employee_id,last_name,Manager_id,1 from employees where employee_id=" & root
   CreateTree root, 1
End Sub
Private Sub CreateTree(PID As Long, level As Long)
    Dim rst As DAO.Recordset
 
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM employees where manager_id=" & PID & " ORDER BY employee_id")
    Do While Not rst.EOF
       'adding a node for every one
        CurrentDb.Execute "insert into Hie_emp (employee_id,last_name,Manager_id,[Level]) values ( '" & rst!employee_id & "','" & rst!last_name & "'," & rst!Manager_id & "," & (level + 1) & ")"
        CreateTree rst!employee_id, level + 1
        rst.MoveNext
    Loop  'while not .eof
    rst.Close   'rst
End Sub

I made this base, run the code and this erase all the data I've entered into the table, this line of code deletes all data from the table.
CurrentDb.Execute "delete * from hie_emp"
Confused!

[RuralGuy]

Weekend:
You know that
If root = Null Then Exit Sub
...should be:
If IsNull(root) Then Exit Sub
...right?

[weekend00]

RuralGuy, you are right.

Stoly:
CurrentDb.Execute "delete * from hie_emp"
is to clear the result table. source table is employees.

[stoly]

RuralGuy, you are right.

Stoly:
CurrentDb.Execute "delete * from hie_emp"
is to clear the result table. source table is employees.

Told you, I am totaly beginer in access!

http://img641.imageshack.us/i/employee.jpg/ This is example!

I do not understand why a skips members, but i will try work with this code later. Thanks a lot for the help.