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

[MajP]

t 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?

I will say it one more time, not sure what is not clear. If you are stuck on doing this with a query, well good luck. If you can switch to a SQL Server back end there are native recursive queries. In Access You do this in code and persist the solution and do not do it in a query. If you need a temp table so be it. This should take a second or two for your little 2k data set if you persist the solution to a table. You already said you plan to use this to update another table, so what is the problem? The problem with the query is that every record has to call the UDF. UDFs are pretty slow. That UDF opens a recordset. In my case each record opens a single RS. In your case it is even worse because it opens one for every record and then for each child record in the tree.


If you look at that tree view demo there is a 10K tree that loads in a half second on a single recursive table. Writing to a table would increase the time, but still should be fast.

 

[HASAN-1993]

Ok, I am sorry, I may want a solution to my problem, and I do not know how to explain in a clear way
Now you say if I only want to update a table (and this is what I just want to update) programmatically and it only takes two seconds on the amount of data that I have, how is this ,thank you so much

 

[HASAN-1993]

Or through the tree view, as you said, it does not matter. I want the fastest way, in any way, A method for updating all accounts through the data

 

[MajP]

Now you say if I only want to update a table (and this is what I just want to update) programmatically and it only takes two seconds on the amount of data that I have, how is this ,thank you so much

You need to not do a query. You read your table in code and save the results somewhere. Either you save it to a temporary table or you update some other table. You mentioned you planned to update another table with the recursive results. Describe that table and what field you plan to save it in. As I said, I did not look at @arnelgp solution yet, but

you need another table that will dump the result of your calculation (zzValues).

So I assume he is recommending the same approach. I assume he has a form so that there is some place to call the code. Did, his solution not do what you wanted?

 

[HASAN-1993]

Mr.arnelgp solution takes a minute and 20 seconds

 

[HASAN-1993]

Yes, I want to update the accounts table, but in order to make it easier for you, let's assume that I want to put them in a temporary table. How will the code be in this case

 

[HASAN-1993]

Or if the update is faster, you can consider the data table as the same as the accounts table

 

[MajP]

I have a pretty fast computer and the best I can do is in the range of 2.4 to 3.25 seconds. Not sure if that is workable for you. There is something about this simple set that seems to make it real slow. I indexed it and still this is the best I can do. To speed things up I did not write the sum while traversing. I simply wrote what level deep the record was. Then after the fact I used some code to use sql to update the sums.

 

[The_Doc_Man]

I was trying to look at the sample data sets you generated. I think know this answer from what I saw, but I have to ask to be sure because the data set you showed us was obviously contrived and it is hard to tell just how representative it is of the real data set.

Do you have data sets that branch (have multiple child records from the same parent) yet are considered to be part of the recursion subset of the parent (and grandparent if present)?

If this can actually occur, then imagine a five-generation recursion set where the two-child generation occurs in the 3rd generation. Which means that you have two subsets starting at the fourth generation, and they each have a 5th generation. IF this is something that can happen as I described, then is the running sum you describe for the 2nd generation going to be the sum of BOTH child trees at once? If branches could occur at other levels, does the same principle apply? If branching cannot occur, I need to know that it can't. If such branching can NEVER happen, or if it can only happen at a specific level, that is important too. Because the mental image I am forming might be possible by doing a little prep-step.

There MIGHT be a way to divide and conquer this particular problem with a little bit of crazy thinking. But I need to verify the situation before I spend too much skull sweat over this one because of the cost of having a wrongful image of the problem.

 

[arnelgp]

how about 0.195 seconds?

 

[gemma-the-husky]

A different thought.

is this an accounting system of some sort, and these are accounting codes?
If so, then you why don't the children start with the parent ID.

so rather than this

0	1	0
1	13	1
13	121	1
121	121001	1

ie, why would 121 be a child of 13? That makes no sense.


Have codes
0
01
012
012001
013
013001
013002

Now the total for 01 is simply the sum of every node starting with 01, and you don't need any recursion at all.
The total for 012 is the sum of every node starting with 012.

I assume all these codes are strings, not numbers. They should be strings, so they become left formatted. I think they must be, otherwise you can't have leading zeroes.


As @The_Doc_Man just noted, if this isn't an ordered tree, it becomes a network, which is completely different. You could even have a child that goes back to a node already visited, and then your recursion can never end.

 

[arnelgp]

it need recursion because it is not a simple, parent-child, there are grand-children and grand-grand-children and so forth.

 

[gemma-the-husky]

it need recursion because it is not a simple, parent-child, there are grand-children and grand-grand-children and so forth.

Yes, but I think it might just be a poor example of the real problem. The fact that are multiple levels might not be critical.

In a tree of account codes you might need a much more detailed expansion for departmental costs, compared with departmental sales, but they all collapse up to a departmental total. You just get a different level of "explosion" available, depending on the cost centre.

 

[arnelgp]

if there is a "systematic" numbering of account, it is straight calculation.
but if main accounts have "01" and another main account starts with "010" (meaning no limit of digit as long as unique).
therefore on main account "01", you will sum all those starting with "01", which will not work because of another main account "010".

 

[MajP]

,I added @arnelgp logic. I can get it to .29 seconds.
If interested in how, Arnelgp got such an improvement some has to do with this specific problem set. This problem set has 4224 records however only 20 have offspring (children, grandchildren). If you avoid making a recursive call to those without offspring you save a ton of time.

In this problem you want to avoid a recursive call for all of those records that do not have offspring. Normally you would not worry about doing this check, and in fact if this was a more broad tree it could be more expensive to do the check. Since this is an extremely narrow tree the check is a must for speed.

@arnelgp,
With this logic (not checking things without offspring) you probably adapt this back to a query like the original problem. I have not tried it yet. It probably be relatively fast. However, IMO this is still a better approach for general recursion to persist the data into a table.

 

[gemma-the-husky]

if there is a "systematic" numbering of account, it is straight calculation.
but if main accounts have "01" and another main account starts with "010" (meaning no limit of digit as long as unique).
therefore on main account "01", you will sum all those starting with "01", which will not work because of another main account "010".

You wouldn't have a main account starting with 01 and another starting with 010.
You need to have enough significant groupings in your structure to manage this.
eg, You could start the main headings with two Alphas, say AA to ZZ, giving you 676 main cost centres. and enough alternatives at each sub-division. Let's say you are a supermarket chain with loads of supermarkets.

At level 1, you might need AA to ZZ
At level 2 you might need 001 to 999 to indicate stores, maybe with 001 to 099 reserved for head office. If you grow super big, this might need expanding to a four digit string, rather than 3.

The idea is, you have an ordered hierarchical structure, with enough space for any amount of growth, and then you don't need recursion., just simple queries.

Then as well being able to summarise your results, it's also easy to pull out all the details for a single store, say store 431, because they will all have 431 in level 2, of the account designation.

 

[HASAN-1993]

I thank everyone for their interest, my dear brothers. This is actually a tree of accounts for an accounting program. I will attach an illustration

 

[HASAN-1993]

This is a simple example of the topic, but there may be more than one tree belonging to a main account, and each calculates the sum of its children

 

[HASAN-1993]

Mr. @MajP Thank you for the effort you made, a wonderful job. This code did the addition process within five seconds. I think this is the fastest possible speed. I hope it will be faster, but it seems that this is not possible.

 

[arnelgp]

did you see the db on post #30, it execute between 0.182~0.195 seconds (almost instant).