Geek Answers Handbook

MS ACCESS - hierarchical tree sorting

I am struggling with a sort problem.

I have a table that looks like this:

aspect_id (int) aspect_text (memo) root_id (int) which has as a foreign key a aspect_id

I have a non-cyclic tree with the following dummy data:

 aspect_id aspect_text root_id 1 root null 2 aspect1 1 3 aspect2 1 4 aspect3 2 5 aspect5 4

In the example, the data is sorted correctly, but not in my database. I want to sort that it starts from the root element, then finds the child element, displays that child element and does it recursively.

With CTE, this is quite doable. Access does not support this. With CTE, this would be something like:

 WITH aspectTree (aspect_id, root_id, Level#) AS ( Select aspect.aspect_id, aspect.root_id, 0 FROM aspect WHERE aspect.aspect_id = 44 UNION ALL SELECT aspect.aspect_id, aspect.root_id, T.Level# + 1 FROM aspect INNER JOIN aspectTree AS T On T.aspect_id = aspect.root_id ) SELECT * FROM aspectTree;
+4
source share
3 answers

If performance is not considered, this rather simple solution will work:

 Public Function GetLevel(ByVal lngNodeId As Long) As Long Dim varRootId As Variant varRootId = DLookup("root_id", "aspect", "aspect_id=" & lngNodeId) If IsNull(varRootId) Then GetLevel = 0 Else GetLevel = GetLevel(varRootId) + 1 End If End Function

You can then use this function in your ORDER BY clause:

 SELECT aspect.* FROM aspect ORDER BY GetLevel([aspect_id]), aspect_text
+1
source

I don’t know if the following will work for you, but here you are using Bill of Materials algorithms.

0
source

Its a complete test code, but I did something that works in vb code. It is really ugly and slow, but it works. Im now cleaned it, just got his job. The solution is a recursive function. The function calls itself if it detects that node has children. It seemed like they were overwriting arrays, so an array of arrays. The code is terrible, but it works, and that’s all I need. The database will and will remain small (<1000 records), so speed is not a problem. Thanks for the comments and answers, if someone knows that I have better decided, I would like to hear it.

  Private Function Fillarray (value As Integer)
 Dim done as boolean

 j = j + 1
 esql = "select aspect_id from aspect where root_id =" & value
 Set rec (j) = db.OpenRecordset (esql)
 Dim k as integer
 k = j
 Do While Not rec (k) .EOF
 done = True
 arra (i) = rec (k) .Fields (0) 
 Dim temp1 As String
 temp1 = DLookup ("[aspects]", "[aspect]", "[aspect_id] =" & rec (k) .Fields (0))
 db.Execute "INSERT INTO sortedaspect (aspect_id, aspect) VALUES (" & rec (k) .Fields (0) & ", '" & temp1 & "')"

         esql = "select aspect_id from aspect where root_id =" & rec (k) .Fields (0)

         Set rec (90) = db.OpenRecordset (esql)
         Do While Not rec (90) .EOF And done
             'fix this without a loop, you only need to know if it has childs ...
             Fillarray (rec (k) .Fields (0))
             done = False

         Loop
       'next child

 rec (k) .MoveNext
 'value = arra (i)
 i = i + 1
 'MsgBox arra (i - 1)
 Loop

 End function
0
source

More articles:


All Articles