Know the children of an account in the account tree by vba or query

[HASAN-1993]

Hello all
I want to know the children of an account in the account tree
I have attached a picture on the topic
I have a table and I want to extract from it the children of a specific account. Is there a way through the vba or sql?
Thanx

 

[June7]

Do you really want accounts 1 and 11 if entered value is 1?

If you really only want account 1, maybe something like:

SELECT * FROM table WHERE Left(fieldname, Len([enter parent])=[enter parent];

 

[HASAN-1993]

I guess it won't do this
Because the table could be like this
I think this has something to do with recursion

 

[HASAN-1993]

In addition, it is possible that the father’s account does not include the son’s numbers
The code should go down the ladder step by step

 

[MajP]

If you wanted to do this in SQL and it is recursive data, I usually have two additional fields in my table. One to store the Top node and one to store the level. Then I run recursive code to update the node and the level for each record.

Then you can filter on TopNode, and sort by level, and then ID.

 

[June7]

I was hoping to avoid recursion. Access doesn't handle it well. It is a common topic. Have you searched?

I don't understand table structure. Is there really only one field? How do you order records so as to 'go down the ladder'? My suggestion didn't care about record order. Also don't understand "possible that the father’s account does not include the son’s numbers".

Are you saying 11 is a child of 1?

 

[MajP]

@June7 ,
here is the background

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

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...

www.access-programmers.co.uk

 

[HASAN-1993]

In addition, it is possible that the father’s account does not include the son’s numbers
The code should go down the ladder step by step

Ok, anything that brings me to this Result

 

[HASAN-1993]

I was hoping to avoid recursion. Access doesn't handle it well. It is a common topic. Have you searched?

I don't understand table structure. Is there really only one field? How do you order records so as to 'go down the ladder'? My suggestion didn't care about record order. Also don't understand "possible that the father’s account does not include the son’s numbers".

Are you saying 11 is a child of 1?

Yes
Because these accounts are called accounting, the qualitative account
It collects accounts from various accounting account trees

 

[MajP]

Yes, my brother

I might be wrong, and now a days you cannot assume anything, but I think June might be a Sister.

 

[HASAN-1993]

I might be wrong, and now a days you cannot assume anything, but I think June might be a Sister.

I did not notice this matter
It was modified before misfortune occurred, which I had not anticipated

 

[MajP]

I might be wrong. In English that is a common female name, but I had to google and learned it used to be a common boy name as well.

June (given name) - Wikipedia

en.wikipedia.org

 

[HASAN-1993]

Ummm
The news today with money tomorrow for free

I might be wrong. In English that is a common female name, but I had to google and learned it used to be a common boy name as well.

June (given name) - Wikipedia

en.wikipedia.org

 

[MajP]

I had to search that and came up with an Egyptian Proverb

News that's for money today will be for free tomorrow

I assume that means that current information may be valuable today (like financial information) but of little use later.

Anyways, would thread #5 work?

 

[HASAN-1993]

I had to search that and came up with an Egyptian Proverb

I assume that means that current information may be valuable today (like financial information) but of little use later.

Anyways, would thread #5 work?

Haha I heard this proverb before and I felt it was right for the confusion we are in

 

[HASAN-1993]

Anyway for the solution
What I want exactly is the result that is in the picture.
If it only works on the same table, we will Insert it to another table
But what I want is for the result to be on another table

 

[MajP]

If that is a self refencing table and you can add to the table the two fields "TopAccount" and "Level"

ID  TopAccount  Level
1       1             1
11      1             2
1001   1             3
1002   1             3

You can first span the table recursively and set the TopAccount and Level for each record. Then Simply

Select ID from SomeTable Where TopAccount = 1 Order by Level, ID

If you have no privileges to the table and can create a Temp table then make a temp table to save the ID, TopAccount, and Level.

 

[HASAN-1993]

The top account for the accounts could not be determined
Because it is possible for the user to choose Account 11 and request his children
It is not a requirement that the higher account be specified
In addition, as I said recently, it is possible for the father's account to be completely different from the son's account
So I said we should go down the stairs step by step
As we previously did with recursive sum

 

[MajP]

I would do it the same way. Have a form an pick your Account that you want all the subaccounts for. Span from that node down and set the Level and Parent. Basically you are querying a branch.

If I wanted from 11 down it updates the TopAccount and Level from there.

ID  TopAccount  Level

11     11             1
1001   11            2
1002   1 1           2

You just have to run the code to update before running your query.
If I get a chance I will demo.

 

[HASAN-1993]

Wow
That would be very nice