Hierarchy summing

[dskysmine]

One way is with DLookup.

Value from field of recordset current record could be in the WHERE CONDITION. I am not quite following your code logic, but something like:

DLookup("NodeID", "tablename", "ParentID=" & rs!ParentID)

So...

Sub Calculations()
'define variables
Dim db As DAO.Database
Dim qryPar As DAO.QueryDef
Dim levels As Long
Dim pl As Double
Dim rsTemp As DAO.Recordset
Dim rsResults As DAO.Recordset
Dim strSQL As String
Dim FindRecordCount As Long
Dim i As Long
Dim fieldPosition As Long
On Error GoTo ErrorHandler

    Set db = CurrentDb()
    levels = DMax("LEVEL", "T_example")
    DoCmd.SetWarnings False
    'loop to go through the hierarchy by levels
    While levels > 1
        'create a temporary table called T_Results
        Set qryPar = db.QueryDefs("q1")
        qryPar.Parameters("Child level") = levels
        qryPar.Execute
        
        'Create a query to sum current level to appropriate parent
        strSQL = "SELECT * FROM T_example LEFT JOIN T_Results ON T_example.NODEID = T_Results.PARENTID"
        
        'Calculate the New value going through each Record in the joined query
        Set rsResults = db.OpenRecordset(strSQL, dbOpenDynaset)
                rsResults.MoveFirst
        
        Do While Not rsResults.EOF
        'Processes the calculation
            rsResults.Fields.Item("Data_Value").Value = rsResults.Fields.Item("Data_Value").Value + rsResults.Fields.Item("SumOfData_Value").Value
            rsResults.MoveNext
        Loop
    
        levels = levels - 1
        DoCmd.RunSQL "DROP TABLE T_Results"
    Wend
    DoCmd.SetWarnings True
ErrorHandler:
   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description

End Sub

but the recordset is not updatable...
How can I put it updatable?

[June7]

I would have to work with the data and code if you want to provide file. Follow instructions at bottom of post.

[dskysmine]

OK!!!
Finally got it to work.
Consider T_example as the table I put with the hierarchy.
And consider q1 as:
SELECT T_example.PARENTID, Sum(T_example.Data_value) AS SumOfData_Value INTO T_Results
FROM T_example
WHERE (((T_example.LEVEL)=[Child level]))
GROUP BY T_example.PARENTID;

Option Compare Database

Sub Calculations()
'define variables
Dim db As DAO.Database
Dim qryPar As DAO.QueryDef
Dim qryTemp As DAO.QueryDef
Dim qryTemp2 As DAO.QueryDef
Dim rsResults As DAO.Recordset
Dim strSQL As String
Dim pl As Double
Dim levels As Long
Dim FindRecordCount As Long
Dim i As Long
Dim fieldPosition As Long
'On Error GoTo ErrorHandler
    Set db = CurrentDb()
    levels = DMax("LEVEL", "T_example")
    DoCmd.SetWarnings False
    'loop to go through the hierarchy by levels
    While levels > 1
        'create a temporary table called T_Results that determines Data_Value to sum upwards in current level
        Set qryPar = db.QueryDefs("q1")
        qryPar.Parameters("Child level") = levels
        qryPar.Execute
        qryPar.Close
        Set qryPar = Nothing
        
        'Create a query to sum current level to appropriate parent
        Set qryTemp = db.QueryDefs("q3")
        qryTemp.SQL = "SELECT *  INTO T_Temporary FROM T_example LEFT JOIN T_Results ON T_example.NODEID = T_Results.PARENTID"
        qryTemp.Execute
        qryTemp.Close
        Set qryTemp = Nothing
        
        'Calculate the New PL going through each Record in the joined query
        Set rsResults = db.OpenRecordset("SELECT * FROM T_Temporary", dbOpenDynaset)
       
        Do While Not rsResults.EOF
        'Processes the calculation
            rsResults.Edit
            rsResults.Fields.Item("Data_Value").Value = rsResults.Fields.Item("Data_Value").Value + rsResults.Fields.Item("SumOfData_Value").Value
            rsResults.MoveNext
        Loop
        
        rsResults.Close
        Set rsResults = Nothing
        
        'Puts the new Data_Value into T_Example
        Set qryTemp2 = db.QueryDefs("q2")
        qryTemp2.SQL = "UPDATE T_example INNER JOIN T_Temporary ON T_example.NODEID=T_Temporary.NODEID SET T_example.Data_Value = T_Temporary.SumOfData_Value WHERE T_Temporary.SumOfData_Value IS NOT NULL"
        qryTemp2.Execute
        qryTemp2.Close
        Set qryTemp2 = Nothing
        
        'Deletes temporary tables
        DoCmd.RunSQL "DROP TABLE T_Temporary"
        levels = levels - 1
        DoCmd.RunSQL "DROP TABLE T_Results"
    Wend
    DoCmd.SetWarnings True
'ErrorHandler:
'   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description

End Sub

It works...
It might not be the perfect solution but here you have it internet...
A working solution to sum hierarchies!
I just leave it here because it took a while and because I think something like this should have already been shared by someone.
Any insights into improving the code or the method are welcome!

Take care!