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

[gemma-the-husky]

@HASAN-1993

You asked previous questions about this recursive accounting tree.
Take it from me, it's an awful idea. A NL/GL accounting structure needs to be carefully planned in a hierarchical manner. You ned to structure the codes to ensure that a child's account number IS the same as the parent but with an extension. Define the account codes as strings.

Then you have a base account of 1
1

if you need to analyse account 1 in more details then have account 11 and 12. But not account 21. 21 is a child of account 2, and not of account 1. If you need to analyse accounts 11 and 12 in more detail then as follows.

11 - if you need a more detailed analysis of account 11 then have
111
112
113
114
115

12 - if you need a more detailed analysis of account 12 then have
121
122
123
124

So don't think of these as numbers. Think of logical breaks in the structure, to give a more detailed analysis, so it works more like this, where the account numbers are strings, so that everything is left justified.

1

1-1 - if you need a more detailed analysis of account 11 then have

1-1-01
1-1-02
1-1-13
1-1-14
1-1-25

1-2 - if you need a more detailed analysis of account 12 then have

1-2-01
1-2-02
1-2-13
1-2-14
1-2-25

so all of these accounts are children of account 1. (You will also have a systematic set of accounts which are children of account 2. )

accounts 1-1-01 to 1-1-25 are children of account 1-1
accounts 1-2-01 to 1-2-25 are children of account 1-2

there is no recursion needed, and it's very easy to manage,

Note that this way, the accounts with 13 as the code at level 3 (coloured blue above all represent a similar "heading", and you can easily get an overall total for your organisation by summing all the transactions with 13 as the code in section 3.

so you end up having a formal hierarchy, where changes of the code at each level of the structure indicate a specific entity within your organisation. No recursion, just a simple filter/sort process.

 

[HASAN-1993]

Thank you Jemma
But what I want is not limited to this hierarchical form
In the account statement if you want more than one account in one statement
There is an account in the accounting software that collects several accounts together, so it is possible to call this account 6 and this account combines the account of Example 34 and Account 87
This method is impossible to work except in recursion

 

[June7]

My father's brothers were named June and Meredith. My father was Robert Cornelius.

I was named after their mother - Hazel June.

Very old fashioned.

 

[arnelgp]

see if this will help.

 

[MajP]

Identify what records are in the "branch" is part of the issue. I assume you want it your results in Tree order. You want to see children under their parents, as if you read the tree from the top down.

To make this easier to see I put it into a tree view. Select a node from the tree, and build the query.
It does the recursive call and I save the TopAccount the Level (not really needed), and more importantly the Tree Sort Order. Notice in the query
2,21,22 followed by the children 221, 222, 223, Then 23, 24, 25. Same order as in the tree.

Private Sub cmboQuery_AfterUpdate()
  UpdateTable Me.cmboQuery
  CreateQuery Me.cmboQuery
  Me.subFrmFileFolderCounts.Form.RecordSource = "qryAccountBranch"
  Me.subFrmFileFolderCounts.Form.Requery
End Sub

'Initialize and log in the Top Account
Public Sub UpdateTable(TopAccountID As Long)
   'Parent and Child IDs are numeric see the instructions on concatenation to make them strings
   Dim strCriteria As String
   Dim bk As String
   Dim AccountID As Long
   Dim ParentID As Long
   Dim AccountName As String
 
   TreeSort = 1
   Set RS = CurrentDb.OpenRecordset("Select * from tblAccounts", dbOpenDynaset)
   strCriteria = "AccountID = " & TopAccountID
   RS.FindFirst strCriteria
 
   If RS.NoMatch Then
    MsgBox "There is no record with an AccountID of " & TopAccountID
   End If
   LogAccount TopAccountID, TopAccountID, 1
   UpdateRecursiveBranch TopAccountID, TopAccountID, 1
   RS.Close
   Set RS = Nothing
End Sub

'Recursive Call
Private Sub UpdateRecursiveBranch(ByVal ParentID As Variant, TopAccount As Long, ByVal NodeLevel As Integer)

  On Error GoTo errLabel
  Dim strCriteria As String
  Dim bk As String
  Dim AccountID As Long

  strCriteria = "ParentID = " & ParentID
  RS.FindFirst strCriteria
  NodeLevel = NodeLevel + 1
  Do Until RS.NoMatch
    AccountID = RS.Fields("AccountID")
    LogAccount AccountID, TopAccount, NodeLevel
   bk = RS.Bookmark
   'Recursive call
   Call UpdateRecursiveBranch(AccountID, TopAccount, NodeLevel)
   RS.Bookmark = bk
   RS.FindNext strCriteria
  Loop
Exit Sub
errLabel:
  MsgBox Err.Number & " " & Err.Description & " In addBranch"
  If MsgBox("Do you want to exit the loop?", vbYesNo, "Error In Loop") = vbYes Then
     Exit Sub
   Else
     Resume Next
   End If
End Sub


'Log results
Public Sub LogAccount(CurrentAccount As Long, TopAccount As Long, Level As Integer)
  Dim strSql As String
  strSql = "UPDATE tblAccounts Set TreeLevel = " & Level & ", TopAccount = " & TopAccount & ", TreeSort = " & TreeSort & " WHERE AccountID = " & CurrentAccount
  Debug.Print strSql
  CurrentDb.Execute strSql
  TreeSort = TreeSort + 1
End Sub

'Build query for display
Public Sub CreateQuery(TopAccount As Long)
  Dim qdf As QueryDef
  Dim db As DAO.Database
  Set db = CurrentDb
  Set qdf = db.QueryDefs("qryAccountBranch")
  qdf.Sql = "SELECT * from qryAccts " & _
             "WHERE TopAccount = " & TopAccount & _
             " ORDER BY TreeSort, Treelevel"
End Sub

 

[MajP]

What @gemma-the-husky proposes would make this much easier, but you might not have the ability to modify the source data. However, you may be able to create a temp table or a new field. I realize that I do exactly that in this very long thread.

Question - calculate inbreeding

I do have a difficult project that is similar to what you are on working, that is why I approach you. OK sounds interesting. I would just post it and people will reply. It may be an area that someone has expertise. Unless it is really COI because now I know more about that than ever wanted...

www.access-programmers.co.uk

But basically for each Bird , I find the pedigree (a list of parents). I separate that with a dash. So like what @gemma-the-husky is proposing I create that structure that looks like this.

1-1-01
1-1-02
1-1-13
1-1-14
1-1-25

So even if the naming convention was not used, you basically can create this after the fact, and still use it to filter.
Assume instead of a nice naming convention you just save the chain of IDs for each record

55-123-999-1456

So record 1456 parents are in order 999, 123, 55. You would read the db and update each record. Then if you wanted all of 999 children (sub children) you can simply query on 999's "pedigree"
Where pedigree like "55-123-999*"

You only have to read the datbase once and update the pedigrees. Then when you add a new record you update its pedigree. As long as you do not add parents after children or move sub accounts to another account then this is always synched. If you add parents (which is very uncommon) then you may need more involved code.

This does what @gemma-the-husky suggests, if you do not have the ability to modify the names or keys to create his recommended structure.

 

[HASAN-1993]

see if this will help.

Yes, it helps and helps a lot. It is a small code, but a very strong code, which is exactly what I want to thank you very much. I will try to integrate it with my own program

 

[HASAN-1993]

Identify what records are in the "branch" is part of the issue. I assume you want it your results in Tree order. You want to see children under their parents, as if you read the tree from the top down.

View attachment 89849

To make this easier to see I put it into a tree view. Select a node from the tree, and build the query.
It does the recursive call and I save the TopAccount the Level (not really needed), and more importantly the Tree Sort Order. Notice in the query
2,21,22 followed by the children 221, 222, 223, Then 23, 24, 25. Same order as in the tree.

Private Sub cmboQuery_AfterUpdate()
  UpdateTable Me.cmboQuery
  CreateQuery Me.cmboQuery
  Me.subFrmFileFolderCounts.Form.RecordSource = "qryAccountBranch"
  Me.subFrmFileFolderCounts.Form.Requery
End Sub

'Initialize and log in the Top Account
Public Sub UpdateTable(TopAccountID As Long)
   'Parent and Child IDs are numeric see the instructions on concatenation to make them strings
   Dim strCriteria As String
   Dim bk As String
   Dim AccountID As Long
   Dim ParentID As Long
   Dim AccountName As String

   TreeSort = 1
   Set RS = CurrentDb.OpenRecordset("Select * from tblAccounts", dbOpenDynaset)
   strCriteria = "AccountID = " & TopAccountID
   RS.FindFirst strCriteria

   If RS.NoMatch Then
    MsgBox "There is no record with an AccountID of " & TopAccountID
   End If
   LogAccount TopAccountID, TopAccountID, 1
   UpdateRecursiveBranch TopAccountID, TopAccountID, 1
   RS.Close
   Set RS = Nothing
End Sub

'Recursive Call
Private Sub UpdateRecursiveBranch(ByVal ParentID As Variant, TopAccount As Long, ByVal NodeLevel As Integer)

  On Error GoTo errLabel
  Dim strCriteria As String
  Dim bk As String
  Dim AccountID As Long

  strCriteria = "ParentID = " & ParentID
  RS.FindFirst strCriteria
  NodeLevel = NodeLevel + 1
  Do Until RS.NoMatch
    AccountID = RS.Fields("AccountID")
    LogAccount AccountID, TopAccount, NodeLevel
   bk = RS.Bookmark
   'Recursive call
   Call UpdateRecursiveBranch(AccountID, TopAccount, NodeLevel)
   RS.Bookmark = bk
   RS.FindNext strCriteria
  Loop
Exit Sub
errLabel:
  MsgBox Err.Number & " " & Err.Description & " In addBranch"
  If MsgBox("Do you want to exit the loop?", vbYesNo, "Error In Loop") = vbYes Then
     Exit Sub
   Else
     Resume Next
   End If
End Sub


'Log results
Public Sub LogAccount(CurrentAccount As Long, TopAccount As Long, Level As Integer)
  Dim strSql As String
  strSql = "UPDATE tblAccounts Set TreeLevel = " & Level & ", TopAccount = " & TopAccount & ", TreeSort = " & TreeSort & " WHERE AccountID = " & CurrentAccount
  Debug.Print strSql
  CurrentDb.Execute strSql
  TreeSort = TreeSort + 1
End Sub

'Build query for display
Public Sub CreateQuery(TopAccount As Long)
  Dim qdf As QueryDef
  Dim db As DAO.Database
  Set db = CurrentDb
  Set qdf = db.QueryDefs("qryAccountBranch")
  qdf.Sql = "SELECT * from qryAccts " & _
             "WHERE TopAccount = " & TopAccount & _
             " ORDER BY TreeSort, Treelevel"
End Sub

MajP, thank you for this code​

It is a nice
It will benefit me a lot
It seems that it took a great effort from you, thank you

 

[HASAN-1993]

My father's brothers were named June and Meredith. My father was Robert Cornelius.

I was named after their mother - Hazel June.

Very old fashioned.

It's still a nice name

 

[MajP]

It seems that it took a great effort from you, thank you

No, this one was all pretty much done already based on this last thread

Solved - Multiple search engine with dropdowns

I opened the data files and I am not sure if the structure you sent is what you meant to send. I am assuming you want a top level for Music, Videos, and Audios. That is not what I see. Please look at the zip file and see if that is correct.

www.access-programmers.co.uk

 

[HASAN-1993]

No, this one was all pretty much done already based on this last thread

Solved - Multiple search engine with dropdowns

I opened the data files and I am not sure if the structure you sent is what you meant to send. I am assuming you want a top level for Music, Videos, and Audios. That is not what I see. Please look at the zip file and see if that is correct.

www.access-programmers.co.uk

Wow, you are a perfect person
If I had wanted to create such a code, I would have wanted for several months
This is if you knew how to make it

 

[gemma-the-husky]

I thought this was a GL/NL

Is this rather a AR/SL

If so, then one way to link accounts is to simply have an extra field in the table called Linked (say) and give all the "linked " accounts a common number. Then accounts 6, 34 and 87 could all have a link value of "1", and again you could extract these 3 accounts with a simple query.

 

[MajP]

@HASAN-1993,
I do not have knowledge on this kind of accounting. I have done a lot of hierarchical things like networks, system sub systems, directories, family trees. You may want to look at what @gemma-the-husky is proposing. He seems familiar with the topic.

 

[WAB]

Hasan - I assume this is an accounting structure? Is that right? I am programming accounting since long. I actually try to stop my clients using sub-accounts. For example if its DEBITORS - I need only ONE DEBITOR account - but have a Debitor management linking them to that account and posting the DEBITOR ID in the Booking. If its branches i use projects and budgets for the branch to build it up in the accounting. So I can isolate subsidiaries or special projects and you can see exactly where you earn or loose money.
I feel that the extensive use of sub-accounts in the bookkeeping actually is a historic burden. Before databases became popular you had not much choice as to use a lot accounts and subaccounts for them. If you look at it from the view of normalization of a database, you get the results much easier without of programming effort - just use what a relational database offers.