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:

If I want to show all the childs of Id 3, I would like to end up with the 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)

Answer 1

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.