Best way to do nested query

[davper]

My issue:
I have a list of accounts. And some of those accounts are merely a heading for a group of subaccounts. Each record has a Parent. It is possible (unlikely but possible) that a parent child relationship can go down infinitely. Realistically 4 levels but I have to prepare for the possibility that the rabbit hole goes much deeper.

I was hoping that there might be a way to dynamically loop through this no matter how deep.

Here is what I have so far. I am just doing a simple debug.print for now. But ultimately will create a temp table or if you know a way to make this a query.


Copy this code and run to see example:

Sub CreateTable()
Dim strSQL As String


strSQL = "Select 1 as EID, ""Base Salary"" as PLLine, 7 as Parent INTO tbl_PLAccounts"

DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True

CurrentDb.Execute "INSERT INTO tbl_PLAccounts (EID, PLLine, Parent) Values (2,'Excess of Benefits Cap',1)"
CurrentDb.Execute "INSERT INTO tbl_PLAccounts (EID, PLLine, Parent) Values (3,'Incentives',1)"
CurrentDb.Execute "INSERT INTO tbl_PLAccounts (EID, PLLine, Parent) Values (4,'PILOR',1)"
CurrentDb.Execute "INSERT INTO tbl_PLAccounts (EID, PLLine, Parent) Values (5,'Fringe',7)"
CurrentDb.Execute "INSERT INTO tbl_PLAccounts (EID, PLLine, Parent) Values (6,'Expenses',0)"
CurrentDb.Execute "INSERT INTO tbl_PLAccounts (EID, PLLine, Parent) Values (7,'Salary',6)"
CurrentDb.Execute "INSERT INTO tbl_PLAccounts (EID, PLLine, Parent) Values (8,'Personal',6)"
CurrentDb.Execute "INSERT INTO tbl_PLAccounts (EID, PLLine, Parent) Values (9,'Malpractice Insurance',8)"
CurrentDb.Execute "INSERT INTO tbl_PLAccounts (EID, PLLine, Parent) Values (10,'Personal Relocation',8)"
CurrentDb.Execute "INSERT INTO tbl_PLAccounts (EID, PLLine, Parent) Values (11,'Professional Development',8)"

End Sub

Sub test()
    Call PLLine
End Sub

Function PLLine()
Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim rs3 As DAO.Recordset
Dim rs4 As DAO.Recordset
Dim strSQL As String
Dim lngRecCount As Long

Set db = CurrentDb
lngRecCount = 0

strSQL = "SELECT tbl_PLAccounts.EID, tbl_PLAccounts.PLLine, tbl_PLAccounts.Parent" & _
        " FROM tbl_PLAccounts WHERE tbl_PLAccounts.Parent=0"

Set rs1 = db.OpenRecordset(strSQL)

Do While Not rs1.EOF
    lngRecCount = lngRecCount + 1
    Debug.Print lngRecCount & " ~ " & rs1.Fields(0).Value & " ~ " & rs1.Fields(1).Value
    
    strSQL = "SELECT tbl_PLAccounts.EID, tbl_PLAccounts.PLLine, tbl_PLAccounts.Parent" & _
        " FROM tbl_PLAccounts WHERE tbl_PLAccounts.Parent=" & rs1.Fields(0)
    Set rs2 = db.OpenRecordset(strSQL)
    Do While Not rs2.EOF
        lngRecCount = lngRecCount + 1
        Debug.Print lngRecCount & " ~ " & rs2.Fields(0).Value & " ~ " & rs1.Fields(1).Value & _
            " ~ " & rs2.Fields(1).Value
        
        strSQL = "SELECT tbl_PLAccounts.EID, tbl_PLAccounts.PLLine, tbl_PLAccounts.Parent" & _
        " FROM tbl_PLAccounts WHERE tbl_PLAccounts.Parent=" & rs2.Fields(0)
        Set rs3 = db.OpenRecordset(strSQL)
        Do While Not rs3.EOF
            lngRecCount = lngRecCount + 1
            Debug.Print lngRecCount & " ~ " & rs3.Fields(0).Value & " ~ " & rs1.Fields(1).Value & _
                " ~ " & rs2.Fields(1).Value & " ~ " & rs3.Fields(1).Value
            
            strSQL = "SELECT tbl_PLAccounts.EID, tbl_PLAccounts.PLLine, tbl_PLAccounts.Parent" & _
            " FROM tbl_PLAccounts WHERE tbl_PLAccounts.Parent=" & rs3.Fields(0)
            Set rs4 = db.OpenRecordset(strSQL)
            Do While Not rs4.EOF
                lngRecCount = lngRecCount + 1
                Debug.Print lngRecCount & " ~ " & rs4.Fields(0).Value & " ~ " & rs1.Fields(1).Value & _
                    " ~ " & rs2.Fields(1).Value & " ~ " & rs3.Fields(1).Value & " ~ " & rs4.Fields(1).Value
                rs4.MoveNext
            Loop
            
            rs3.MoveNext
        Loop
        
        rs2.MoveNext
    Loop
    
    rs1.MoveNext
Loop

Set rs1 = Nothing
Set db = Nothing
End Function

 

[MarkK]

You need a recursive procedure, which is a procedure that calls itself. Rather than nest a series of blocks of code that are exactly the same, write one block and have that block call itself using whatever parameters it needs to do whatever it does for itself, and its children.
Search for "Recursion".

 

[davper]

Thanks for the syntax but a recursive query is not supported in Access. At least from what I am finding at the moment.

All examples seem to show that I need to know how many levels deep.

 

[spikepl]

You should consider how to store your data.

There are other ways of storing a hierarchical structure than just referring to parentID, see eg this article, that explains one particular algorithm, http://articles.sitepoint.com/article/hierarchical-data-database or google for hierarchical data sql or similar terms.

 

[spikepl]

And besides, #2 is right. And he speaks of a recursive procedure, not a recursive query.

 

[MarkK]

This code block would be better implemented using a recursive procedure. It would be simpler and more effective because it would not be limited to a depth of four.

Do While Not rs1.EOF
    lngRecCount = lngRecCount + 1
    Debug.Print lngRecCount & " ~ " & rs1.Fields(0).Value & " ~ " & rs1.Fields(1).Value
    
    strSQL = "SELECT tbl_PLAccounts.EID, tbl_PLAccounts.PLLine, tbl_PLAccounts.Parent" & _
        " FROM tbl_PLAccounts WHERE tbl_PLAccounts.Parent=" & rs1.Fields(0)
    Set rs2 = db.OpenRecordset(strSQL)
    Do While Not rs2.EOF
        lngRecCount = lngRecCount + 1
        Debug.Print lngRecCount & " ~ " & rs2.Fields(0).Value & " ~ " & rs1.Fields(1).Value & _
            " ~ " & rs2.Fields(1).Value
        
        strSQL = "SELECT tbl_PLAccounts.EID, tbl_PLAccounts.PLLine, tbl_PLAccounts.Parent" & _
        " FROM tbl_PLAccounts WHERE tbl_PLAccounts.Parent=" & rs2.Fields(0)
        Set rs3 = db.OpenRecordset(strSQL)
        Do While Not rs3.EOF
            lngRecCount = lngRecCount + 1
            Debug.Print lngRecCount & " ~ " & rs3.Fields(0).Value & " ~ " & rs1.Fields(1).Value & _
                " ~ " & rs2.Fields(1).Value & " ~ " & rs3.Fields(1).Value
            
            strSQL = "SELECT tbl_PLAccounts.EID, tbl_PLAccounts.PLLine, tbl_PLAccounts.Parent" & _
            " FROM tbl_PLAccounts WHERE tbl_PLAccounts.Parent=" & rs3.Fields(0)
            Set rs4 = db.OpenRecordset(strSQL)
            Do While Not rs4.EOF
                lngRecCount = lngRecCount + 1
                Debug.Print lngRecCount & " ~ " & rs4.Fields(0).Value & " ~ " & rs1.Fields(1).Value & _
                    " ~ " & rs2.Fields(1).Value & " ~ " & rs3.Fields(1).Value & " ~ " & rs4.Fields(1).Value
                rs4.MoveNext
            Loop
            
            rs3.MoveNext
        Loop
        
        rs2.MoveNext
    Loop
    
    rs1.MoveNext
Loop

Cheers,
Mark

 

[davper]

And besides, #2 is right. And he speaks of a recursive procedure, not a recursive query.

A search for Recursive Procedure led me here:
http://support.microsoft.com/kb/132242
This works perfectly.

Searching is tough to do when you don't know what you are looking for.