Get all possible childs/decendants of a parent

Question

I searched for this a lot but couldn't fine anything that is usable for MS Access, I found solutions for SQL but the statements that are used are not allowed in access SQL.

So, in MS access 2019 I have a table tbContentList with an Id and ParentID. What I would like is to show all childs/decendants of a specific parent.

My table look like this:
Original table

If I want to show all the childs of Id 3, I would like to end up with the result:
Query result

Is this possible in MS access queries? It is possible with VBA but I think it's faster to do it with queries. Can anybody help me with this topic please?

the SQL equivalent: https://www.codeproject.com/Articles/818694/SQL-Queries-to-Manage-Hierarchical-or-Parent-child (All possible childs)

Hi. When you say all the childs of Id 3, do you mean all records where ParentID is 3? If so, your example result doesn't show this, and if not, what do you mean exactly? — Sam, Nov 29 '20 at 12:09
Please update your question to include the images to which you have linked. Even better, for the source data, put the actual text in the question so that folks can cut and paste rather than retyping. — freeflow, Nov 29 '20 at 12:42
Does this answer your question? [Is it possible to create a recursive query in Access?](https://stackoverflow.com/questions/763016/is-it-possible-to-create-a-recursive-query-in-access). You want all descendants of 3? This is not easy with Access and accomplishing with SQL alone is virtually impossible. Need VBA. Also review https://www.codeproject.com/questions/338969/ms-access-query-to-retrieve-hierarchical-records and http://www.fabalou.com/Access/General/genealogy_example.asp. I am sure you can find more sample Access dbs. — June7, Nov 29 '20 at 12:45
Hello All, thanks for the quick feedback. Unfortunately if you are new you are not allowed to include images. I will update when possible. — HerrMaler, Nov 29 '20 at 13:53

score 2 · Accepted Answer · answered Nov 30 '20 at 10:26

So, I was able to modify the logic from Gustav and make it suitable for my project. I put the parent result between a delimiter ";". This makes it easier to find the descendants of a specific ContentID in the query. Furthermore I had to handle Null values in the parent column since some of the ContentID are the beginning of the the tree.

Public Function GetParentIDs(ByVal lContentID As Long) As String
    Static dbs As DAO.Database
    Static tbl As DAO.TableDef
    Static rst As DAO.Recordset
    Dim strParents As String

    If dbs Is Nothing Then
        ' For testing only.
        ' Replace with OpenDatabase of backend database file.
        Set dbs = CurrentDb
        Set tbl = dbs.TableDefs("tbContentList")
        Set rst = dbs.OpenRecordset(tbl.Name, dbOpenTable)
    End If

    With rst
        .Index = "PrimaryKey"
        Do While lContentID > 0
            .Seek "=", lContentID
            If Not .NoMatch Then
                lContentID = Nz(!ParentID.Value, 0)
                If lContentID > 0 Then
                    strParents = ";" & CStr(lContentID) & strParents
                Else
                    Exit Do
                End If
            Else
                Exit Do
            End If
        Loop
        ' Leave recordset open.
        ' .Close
    End With

    '  Don't terminate static objects.
    '  Set rst = Nothing
    '  Set tbl = Nothing
    '  Set dbs = Nothing

    'Return value
    If strParents = "" Then
        GetParentIDs = ""
    Else
        GetParentIDs = strParents & ";"
    End If
End Function

The query to get all Descendants from a specific ContentID. The 3 if for this example, this could be changed to another value.

SELECT tbContentList.[ContentID], tbContentList.[ParentID], tbContentList.[Item], GetParentIDs([ContentID]) AS Parents
FROM tbContentList
WHERE (((GetParentIDs([ContentID])) Like '*;3;*'));

Thanks for the help and putting me in the right direction.

score 1 · Answer 2 · answered Nov 29 '20 at 13:34

You have several options. One, however, won't do and that is a recursive query using SQL only; Access can't be fooled and will claim about a circular reference. Your only chance is to create a query resolving a limited number of levels only, say, 8 or 10.

But you can cover the recursive call in a domain aggregate function like DLookup(). This is, however, very slow as DLookup() calling the query will run for each and every record. For more than some dozens of records this will most likely be unacceptable.

The fastest way, for an unlimited number of levels, I've found is to create a lookup function which walks the tree for each record. This can output either the level of the record or a compound key built by the key of the record and all keys above.

As the lookup function will use the same recordset for every call, you can make it static, and (for Jet) you can improve further by using Seek to locate the records.

Here's an example from a similar case which will give you an idea:

Public Function RecursiveLookup(ByVal lngID As Long) As String

    Static dbs      As DAO.Database
    Static tbl      As DAO.TableDef
    Static rst      As DAO.Recordset

    Dim lngLevel    As Long
    Dim strAccount  As String

    If dbs Is Nothing Then
        ' For testing only.
        ' Replace with OpenDatabase of backend database file.
        Set dbs = CurrentDb
        Set tbl = dbs.TableDefs("tblAccount")
        Set rst = dbs.OpenRecordset(tbl.Name, dbOpenTable)
    End If

    With rst
        .Index = "PrimaryKey"
        While lngID > 0
            .Seek "=", lngID
            If Not .NoMatch Then
                lngLevel = lngLevel + 1
                lngID = !MasterAccountFK.Value
                If lngID > 0 Then
                    strAccount = str(!AccountID) & strAccount
                End If
            Else
                lngID = 0
            End If
        Wend
        ' Leave recordset open.
        ' .Close
    End With

    '  Don't terminate static objects.
    '  Set rst = Nothing
    '  Set tbl = Nothing
    '  Set dbs = Nothing

    '  Alternative expression for returning the level.
    '  (Adjust vartype of return value of function.)
    '  RecursiveLookup = lngLevel ' As Long
    RecursiveLookup = strAccount

End Function

This assumes a table with a primary key ID and a foreign (master) key pointing to the parent record - and a top level record (not used) with a visible key (AccountID) of 0.

Now your tree will be nicely shown almost instantaneously using a query like this where Account will be the visible compound key:

SELECT
    *, RecursiveLookup([ID]) AS Account
FROM
    tblAccount
WHERE
    (AccountID > 0)
ORDER BY
    RecursiveLookup([ID]);

Thanks Gustav, I will look into this. – HerrMaler Nov 29 '20 at 13:48 — HerrMaler, Nov 29 '20 at 13:48

Get all possible childs/decendants of a parent

2 Answers2