Is it possible to create a recursive sum in ms access by sql query or vba

[HASAN-1993]

i have table like>>

Parent_ID	Acc_ID	value
0	1	0
1	13	1
13	121	1
121	121001	1
0	2	0
2	23	1
23	131	1
131	131001	1

The output I want

Parent_ID	Acc_ID	Value
0	1	3
1	13	3
13	121	2
121	121001	1
0	2	3
2	23	3
23	131	2
131	131001	1

What I want is unlimeted Calculating of all the Nodes of the Accounts tree
But I want a solution that is fast because I have a lot of data and I don't want the query to take long to work, thank you

 

[MajP]

Yes I speak of recursion in great detail.

Hierarchical Data, Recursion, Tree-Views, and a Custom Class to Assist

Would monitor size or resolution have anything to do with it and/or having a modal form? I'll try again when I get back home.

www.access-programmers.co.uk

Unfortunately you can do this, but Access cannot do this in a native query. You need code to get the sum.

 

[MajP]

You may follow this recent one where I sum up the files in a directory and get the level of each folder using recursions. It starts here.

Solved - Multiple search engine with dropdowns

Finally figured it out. I did not see that you had these as FAYT comboboxes as well. Unfortunately you cannot just requery a FAYT combobox. I need to add some notes to the class. You have to set the the rowsource of the FAYT. I thought I was going crazy. Public Function FilterRequery() On...

www.access-programmers.co.uk

 

[MajP]

Can you post a real table with some data. Lots would be good. I will provide the function and query so you can see. Do you need a Treeview? Fast is not going to happen. This is not an efficient means. But depends what you mean for fast. I can load 50,000 records likely under 10 seconds. If you have a million records, it is going to take some time and would not attempt to do that in a query. You will have to persist the solutions to a table, because just scrolling the query will cause a recalc.

 

[MajP]

Also persisting the data can be much faster. Assume you add the field TotalValue. If you do this in a query you would call the recursive function for each Acc_ID. So you span from that Acc_ID downwards for every acount ID. If you persist the data you span once only and write the partial sums as you go along. This is one case where saving calculated values make far more sense then trying to dynamically calculate. Then you prompt the user before looking at the data if they want to update. If not changes then you do not respan.

 

[HASAN-1993]

ok, I do not want a treeview, I just want a query to calculate for me the recurring account for all my tables, and if it is as you say 5000 only need 10 seconds, this is wonderful

 

[HASAN-1993]

I tried to make this query from vba and SQL, but if the recordings are greater than 200 it becomes unreasonably slow.

 

[MajP]

I was hoping to have a reasonable dataset. As large as you can provide. No more than 10k though, if possible.

 

[arnelgp]

see Query1.
see also the function in Module1.

 

[HASAN-1993]

First, thanks to everyone for the interest. Secondly, this is a file that tells about the thing that I want, Mr. arnelgp your code. It did not help me because I need to collect the tree to the main Nodes main node is 1 , see this file plz, thank you

 

[HASAN-1993]

Mr. Majp, these are about 4,200 records if that helps

 

[HASAN-1993]

This code that I created is working correctly, you can see it, but it is very slow and I want a faster way

 

[HASAN-1993]

Note: the Nodes belonging to each other may be more than 15 Nodes

 

[arnelgp]

that is not the result you showed on post#1.

 

[HASAN-1993]

Ok, I am very sorry, but it was a simple example, so you can understand what is meant

 

[arnelgp]

you need another table that will dump the result of your calculation (zzValues).
you also need a Form (datasheet) so we can reset the Records in zzValues
whenever we run Query1.

initially it will take time to calculate (since recursive).
after the records are shown in the form, it will be smooth to
browse the records.

do not directly run Query1 but instead use the Form (RunForm).

 

[HASAN-1993]

Thank you, brother, but I already want the query to update other tables, which are the accounts table, and the time that this code takes is very long
Look, this query, it will update the accounts every time the user enters an invoice and this is a very long time to add an invoice

 

[MajP]

Fast is not going to happen. This is not an efficient means. But depends what you mean for fast. I can load 50,000 records likely under 10 seconds. If you have a million records, it is going to take some time and would not attempt to do that in a query. You will have to persist the solutions to a table, because just scrolling the query will cause a recalc.

So as I said in the beginning, I would not attempt this in a query. The code you wrote is super inefficient, in that it opens a recordset on every traversed node. If you had deeper branches this would get even more inefficient. Each recordset stays open until all child nodes are traversed. So you can make that code much faster. This version using a single open recordset is 5-10 times faster

Public Function GetRecursiveSum(ByVal accountID As Long) As Double

'On Error GoTo errLabel
  Dim strCriteria As String
  Dim bk As String
  Dim currentID As Long
  Dim CurrentLevel As Integer
  Dim CurrentValue As Double
  Dim TotalValue As Double
  If rs Is Nothing Then
      Set rs = CurrentDb.OpenRecordset("Select * FROM Table2 order by accountid")
      'only set on first call
  End If
  rs.FindFirst "accountID = " & accountID
  ' if this is the base node need to ensure you move to it
  CurrentValue = rs.Fields("value")
  strCriteria = "ParentID  = " & accountID
  'Debug.Print strCriteria
  rs.FindFirst strCriteria
' CurrentValue = RS.Fields("value")
  Do Until rs.NoMatch
    currentID = rs.Fields("AccountID")
     bk = rs.Bookmark
    CurrentValue = CurrentValue + GetRecursiveSum(currentID)
    rs.Bookmark = bk
    rs.FindNext strCriteria
  Loop
  GetRecursiveSum = CurrentValue

Exit Function
errLabel:
  MsgBox Err.Number & " " & Err.Description & " In addBranch"
  If MsgBox("Do you want to exit the loop?", vbYesNo, "Error In Loop") = vbYes Then
     Exit Function
   Else
     Resume Next
   End If
End Function

But NO way is that Fast enough. At least you can scroll without locking up your computer. You may be able to run an update query with it.

I have not had the time to look at @arnelgp solution, but I am assuming he also suggests a persisted solution. When I do regression in access to any scale I am writing results to a temp table or have fields in the source table to store the regressed values.
Doing this with a temp table, I can do in a few seconds with a stable solution.

 

[HASAN-1993]

It also took 20 seconds, I have an idea whether it is possible to make this query on another data base and run the query from another database so that the user can work and work the query on another database in this case the user will not wait 20 seconds until it finishes and completes its work

 

[HASAN-1993]

It is impossible that there is no solution to this problem.
In any way, I want to activate this query update accounts without disrupting the user
Is it reasonable that not all Access programmers have a solution?