Access: Traversing the hierarchical organization with VBA - With recursive calls or BFS?

I am currently developing an employee DB at the company where I'm doing my internship.  One of the functions of this Access database will be, to create a photo book (with a report) of all the employees - from the CEO all the way down. I, therefore, have to traverse the tree in such a way that all top-level executives and their assistants come first and then each department and their teams.  This is what the company structure looks like:

hierarchy of the organisation
And this is how the organizational units are stored in the database:

As stored in Access
I am using the following code, which calls itself recursively to traverse the tree/graph:

Function preorderProcessing(parentID As Integer, level As Integer)

    Dim MyDB            As DAO.Database
    Dim MyRS            As DAO.Recordset
    Dim X               As Integer
    Dim colOrgUnits     As Collection

    
    Set MyDB = CurrentDb()
    Dim qdf As DAO.QueryDef
    Set colOrgUnits = New Collection
    
    'get the children of the current organizational unit (parentID)
    'when the function runs for the first time, we are looking for
    'the parentID 0, the CEO, as he has no children.
    
    If parentID = 0 Then
        Set qdf = MyDB.QueryDefs("qyrOrgUnits_SearchParentID_Null")
        'first run, org Unit 4
        DoCmd.RunSQL "DELETE * FROM tblOrgHierarchy"
    Else
        Set qdf = MyDB.QueryDefs("qryOrgUnits_SearchParentID")
        qdf.Parameters("parentID") = parentID
    End If
    
    
    Dim rs As DAO.Recordset
    Set rs = qdf.OpenRecordset
    
    'add all the children of the current parentID to a collection
    Do While Not rs.EOF
      colOrgUnits.Add (rs.Fields("org_OrganisationUnitID"))
      Debug.Print "Level: " & level
      Debug.Print GetOrgUnitName(rs.Fields("org_OrganisationUnitID"))
      rs.MoveNext
    Loop
    rs.Close
    
    Debug.Print "==================================================="

    
    Dim orgUnitID As Integer
    Dim sql As String
    

    
    'populate table tblOrgHierarchy
    'and then recursevely call the same function again with all the children
    For X = 1 To colOrgUnits.count
        orgUnitID = colOrgUnits.Item(X)
         sql = "INSERT INTO tblOrgHierarchy (hie_OrganisationalUnitID, hie_OrgUnitName, hie_Manager, hie_Type, hie_level, hie_OrgCode, hie_ParentID)" _
                & "VALUES (" & orgUnitID & ", '" & GetOrgUnitName(orgUnitID) & "' , '" & GetOrgUnitBoss_FullName(orgUnitID) & "' , '" & GetOrgUnitType(orgUnitID) & "' , '" & level & "' , '" & getOrgCode(orgUnitID) & "' , '" & parentID & "' );"
         MyDB.Execute sql
         preorderProcessing colOrgUnits.Item(X), level + 1
    Next X
    
    
End Function

Open in new window


This is what the table tblOrgHierarchy looks like after the query is run:

result after the recursive function is run
As you can see, the department with the ID 835 - the Corp. Development - is at the bottom of the table - it should be at the top where the assistants are.
The output of the recursive function should be the

  • CEO first,
  • then the yellow layer and then
  • the green/blue/orange/red block - the departments and their teams,

I suspect I have to make use of a modified version of the breadth-first-search algorithm, but I'm not sure. The algorithm will have to check whether the organisational unit being processed has any children. If it doesn't have any children, it should be added to the table tblOrgHierarchy. If it does have children, it should be put into a stack for later processing..

How would you solve this? How do you deal with hiearchies?  Should the organisational units be stored differently in the table for this to work or is it OK the way they are?

I have attached an Access DB to this post with the relevant code, forms and data. The function is in the module basPhotobook.

Thank you for your kind assistance.

Massimo
theDB.accdb
Massimo ScolaSoftware EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
It's not a spreadsheet but a table, so all you need is to apply the sorting you request - on the level:

org.PNG
ste5anSenior DeveloperCommented:
First of all: Do you want a correct relational data model or just something that works somehow?

Then: I would use a nested set instead of an adjacency list to store the hierarchy, E.g. BOM, with Joe Celko Nested Sets.
Massimo ScolaSoftware EngineerAuthor Commented:
I have already tried to sort the table tblOrgHierarchy.
What happens then is, that although the organisational Units are grouped by Level, their children are not grouped / assigned to their parents anymore.

table tblOrgHierarchy sorted by Level
I would like to have a correct relational model - see my attached ER diagram.

Does anyone have a resource how BFS is created in VB / VBA?
Or is there a way of doing it without using the BFS algorithm?
Sanagate-ERD-E.png
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Gustav BrockCIOCommented:
I'm not sure I follow.

As you can see, the department with the ID 835 - the Corp. Development - is at the bottom of the table - it should be at the top where the assistants are.
The sorting on level fixed that.

children are not grouped / assigned to their parents anymore.
Wouldn't that be the hie_parentId? Seems nicely grouped to me.

So what should it look like, please?
Massimo ScolaSoftware EngineerAuthor Commented:
The problem is that the sorted table does not look like the hierarchy

OrgChart.pngtable.PNG
I would like all children org Units to be next to their parent .. .. or is this something that needs to be done within a report?
The Report should have the following structure:

CEO
COO
Corp. Development
Assistant
New Assistant

Claims <--
Claims Management
..
Customer Service
Avor
..
VAD&UWR
VAD
..
Marketing and Services
IT
..
Gustav BrockCIOCommented:
Then you could sort by OrgCode and Level.
I see no other way to tell why, say, Claims should be sorted before (or after) CustomerService.
ste5anSenior DeveloperCommented:
Well, start with proper normalization. Currently there are too many redundant columns.

- tblOrganisationalUnits:
  1) There should be no self-join for an adjacency list. This should be in the hierarchy table.
  2) Supervisors and deputies should be separate tables.
- tblOrgHierarchy_
  1) OU should be a lookup (foreign key), not a text copy.
  2) Manager should be a separate table, not a text column. How do the manager differ from the supervisor or the deputy? Possible subclassing.
  3) What is OrgCode?
  4) The adjacency list should be built over the hierarchy ID, not the OU ID.
  5) The hierarchy table should have some calculated columns: Level, Path and in the case of multiple hierarchies Root. These properties are calculated by your intended recursive method. And using the path as sort order for reporting will give you the necessary order in a tabular view. The algorithm is straight forward: Select all rows with no parent. For each start the recursive call.
 
Option Compare Database
Option Explicit

Public Sub CalculateHierarchy()

  Const SQL_ROOT_NODES As String = "SELECT * FROM tblOrgHierarchy WHERE hie_ParentID IS NULL;"

  Dim rs As DAO.Recordset

  Set rs = CurrentDb.OpenRecordset(SQL_ROOT_NODES)
  If Not rs.BOF And Not rs.EOF Then
    Do While Not rs.EOF
      CalculatedHierarchyNode rs![hie_HierarchyID], 0, "\\"
      rs.MoveNext
    Loop
  End If
  
  rs.Close
  Set rs = Nothing

End Sub

Private Sub CalculateHierarchyNode(AHierarchyID As Long, ALevel As Long, APath As String)

  Const SQL_CHILD_NODES As String = "SELECT * FROM tblOrgHierarchy WHERE hie_ParentID = ?;"
  Const SQL_CURRENT_NODE As String = "SELECT * FROM tblOrgHierarchy WHERE hie_HierarchyID = ?;"
  
  Dim rs As DAO.Recordset
  
  Dim Path As String
  
  ' Adjust current node.
  Set rs = CurrentDb.OpenRecordset(Replace(SQL_CURRENT_NODE, "?", AHierarchyID))
  rs.Edit
  rs![level] = ALevel
  Path = APath & rs![hie_HierarchyID] & "\"
  rs![Path] = Path
  ' HACK: The current model is redundant. Thus a separate lookup is necessary.
  AHierarchyID = rs![hie_OrganisationalUnitID]
  rs.Update
  rs.Close
  Set rs = Nothing
  
  ' Recursive call for all children.
  Set rs = CurrentDb.OpenRecordset(Replace(SQL_CHILD_NODES, "?", AHierarchyID))
  If Not rs.BOF And Not rs.EOF Then
    Do While Not rs.EOF
      CalculatedHierarchyNode rs![hie_HierarchyID], ALevel + 1, Path
      rs.MoveNext
    Loop
  End If

  rs.Close
  Set rs = Nothing

End Sub

Open in new window

Level is a long integer column and Path a long text column in the hierarchy table.

btw, What is the use for this database? Cause in HR you need ValidFrom, ValidTo basically for every row to make adjustments in advance. You also need a lot of history tables (data warehouse) for historical reporting.
John TsioumprisSoftware & Systems EngineerCommented:
Maybe i am reading it wrong but just sorting by hie_ParentID and then by OrganisationalUnitID wouldn't do the job?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.