MajP
You've got your good things, and you've got mine.
- Local time
- , 23:15
- Joined
- May 21, 2018
- Messages
- 8,845
This is part two to this thread.
However the original thread dealt more specifically with tree views. This does not.
The above threads has many links to other treeview and recursion demos.
The example used in this thread started from the below thread.
1. Self Referencing Table. A self referencing tables is a table that has both Parent and Child records in the same table. So each record has a foreign key storing the Parent ID. This is mostly associated with Hierrachical data where the depth of the levels is unknown. This can be used to model Systems and Subsystems, People (or animal) Genealogy, File structures, etc.
The below table shows Sales Agents and who they report to. At the highest level you have some options on how to model this. I always have the top level parent (reportsTo) report to no one. I leave this field null. You can have them report to themselves. Either will work and support referential integrity. See discussion here
tblAgents tblAgents
Notice that at the top level Allen, Charlie, and Bethany report to no one.
2. Self Referencing tables make working with hierarchical data very easy to add, edit, and delete. However, this structure is hard to pull out results and display information. To get information out requires recursion. Using recursion and persisting information to a table allows one to show this information in a logical way with or without a tree view.
qryAgentTree qryAgentTree
Once the Level and Tree sort are calculated the 1st column can be made. ( Specifically once the level is calculated the query can put in the correct number of dashes, once the tree sort is calculated the query is sorted properly)
3. Referential Integrity. You can in fact even enforce referential integrity to include cascade deletes and cascade updates.
4. Recursing a Recordset. When working with selfrefencing data in an Access table you need to know how to recurse a recordset and span all the records in hierarchical order. You can use that to populate a tree view or sum/calculate values over a branch. For example you can calculate the number of documents in a folder to include all subfolders. Calculate the number of descedants for a given person in a family tree.
I found to make working with this data much easier is to recurse the data and then store two values. The level at which an item resides and the order that item would appear in a tree if read from top down. I added these to the table.
In this recursion each call passes in a potential parent, starts to read through the child records, and at each child it then calls itself.
In the first pass it loops all records whose ReportTo is Null. It reaches the first record Allen, and then calls itself passing Allen as the parent. Then it loops all records whose parent is allen. It loops to fred then calls itself passing in fred. Then begins to loop all records whose parent is Fred....
All of those loops are still open until it gets out to James. Since it cannot go any further each open call begins to end and it works itself backwards. Once it works back to the call where Parent was Allen it reads Gary.
I see lots of bad versions of recursive recordsets. Often the code is written to open a new recordset on each call to the procedure. This can be extremely slow and eat up a lot of resources. This version opens a recordset on the first call and passes a reference to the recordset on subsequent calls. Storing the BK is important prior to making a recursive call. As the code works it ways back up the branch the code needs to know where it left off before the recursive call.
Hierarchical Data, Recursion, Tree-Views, and a Custom Class to Assist
I have had some people ask questions on working with Hierarchical data and Tree Views, which often involves using recursion. If not familiar with recursion this is where a procedure repeatedly calls itself until hitting a condition where it is told to stop. This is used often where you have to...
www.access-programmers.co.uk
The above threads has many links to other treeview and recursion demos.
The example used in this thread started from the below thread.
link record to another record in the same table
Two things 1. rsProcessed!RoyaltyAgent_ID_FK = DLookup("agentPK", "tblAgents", "AgentName = 'Capital'") This line needs to have the correct name of the Main Agency. If the main agency is Captial1 then this has to match. They do not currently match 2. The rule is that if the inactive date is...
www.access-programmers.co.uk
1. Self Referencing Table. A self referencing tables is a table that has both Parent and Child records in the same table. So each record has a foreign key storing the Parent ID. This is mostly associated with Hierrachical data where the depth of the levels is unknown. This can be used to model Systems and Subsystems, People (or animal) Genealogy, File structures, etc.
The below table shows Sales Agents and who they report to. At the highest level you have some options on how to model this. I always have the top level parent (reportsTo) report to no one. I leave this field null. You can have them report to themselves. Either will work and support referential integrity. See discussion here
Reflexive table and the usual message : You can not add or change record because...
Hello. I have to model a stable, and so stallions, which belong to an owner at a date t etc ... so far so good. Except that the person responsible imposes that only Stallions - not mare, why? I know it's weird, but do not ask me why, this is so - have to be modeled, and so have only one father...
www.access-programmers.co.uk
tblAgents tblAgents
AgentPK | AgentName | ReportsTo |
---|---|---|
1 | Allen | |
3 | Charlie | |
4 | Debbie | 13 |
5 | Earl | 13 |
6 | Fred | 1 |
7 | Gary | 1 |
13 | Bethany | |
15 | Harry | 3 |
16 | Ingrid | 6 |
17 | James | 16 |
2. Self Referencing tables make working with hierarchical data very easy to add, edit, and delete. However, this structure is hard to pull out results and display information. To get information out requires recursion. Using recursion and persisting information to a table allows one to show this information in a logical way with or without a tree view.
qryAgentTree qryAgentTree
Agent | Level | Tree Sort |
---|---|---|
Allen | 0 | 1 |
----Fred | 1 | 2 |
--------Ingrid | 2 | 3 |
------------James | 3 | 4 |
----Gary | 1 | 5 |
Bethany | 0 | 6 |
----Debbie | 1 | 7 |
----Earl | 1 | 8 |
Charlie | 0 | 9 |
----Harry | 1 | 10 |
3. Referential Integrity. You can in fact even enforce referential integrity to include cascade deletes and cascade updates.
4. Recursing a Recordset. When working with selfrefencing data in an Access table you need to know how to recurse a recordset and span all the records in hierarchical order. You can use that to populate a tree view or sum/calculate values over a branch. For example you can calculate the number of documents in a folder to include all subfolders. Calculate the number of descedants for a given person in a family tree.
I found to make working with this data much easier is to recurse the data and then store two values. The level at which an item resides and the order that item would appear in a tree if read from top down. I added these to the table.
Code:
Public Sub RecurseLevels(Optional ParentID As Variant = -1, Optional RS As dao.Recordset = Nothing, Optional Level As Integer = 0, Optional TreeSort As Integer = 0)
'This assigns the levels and tree sort
'if the top level parent ID is null do not pass in anything
Dim strCriteria As String
Dim bk As String
If RS Is Nothing Then
'On the first call to the method open a recordset then pass it in recursion
Dim strSql As String
strSql = "Select * from tblAgents order by AgentName"
Set RS = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
If ParentID = -1 Then
'in this example our top level parent is null
strCriteria = "ReportsTo is Null"
'If not null then simply
'strCriteria = "ReportsTo = " & parentID
RS.FindFirst (strCriteria)
If RS.NoMatch Then
MsgBox "There is no record with a Parent ID of " & ParentID
Exit Sub
End If
End If
Else
'all other calls you pass in the parentID
strCriteria = "ReportsTo = " & ParentID
End If
RS.FindFirst (strCriteria)
Do Until RS.NoMatch
bk = RS.Bookmark
'The tree sort gets updated on every record
TreeSort = TreeSort + 1
RS.Edit
RS!AgentLevel = Level
RS!TreeSort = TreeSort
RS.Update
'The current Record is passed as the parent. The level only gets updated on a recursive call
RecurseLevels RS!AgentPK, RS, Level + 1, TreeSort
RS.Bookmark = bk
RS.FindNext (strCriteria)
Loop
End Sub
In this recursion each call passes in a potential parent, starts to read through the child records, and at each child it then calls itself.
In the first pass it loops all records whose ReportTo is Null. It reaches the first record Allen, and then calls itself passing Allen as the parent. Then it loops all records whose parent is allen. It loops to fred then calls itself passing in fred. Then begins to loop all records whose parent is Fred....
All of those loops are still open until it gets out to James. Since it cannot go any further each open call begins to end and it works itself backwards. Once it works back to the call where Parent was Allen it reads Gary.
I see lots of bad versions of recursive recordsets. Often the code is written to open a new recordset on each call to the procedure. This can be extremely slow and eat up a lot of resources. This version opens a recordset on the first call and passes a reference to the recordset on subsequent calls. Storing the BK is important prior to making a recursive call. As the code works it ways back up the branch the code needs to know where it left off before the recursive call.