Recursive table / report structure 1

[jflachman]

I have a table with a structure that can be simplified to:
Resource ID Autonumber
Name, etc Multiple fields
Manager ID Number (long)

The manager ID is a link to another record in the same table. That person may also have a manager and so on.

I want to write a report that will display, for a given manager, their entire organization. Grouped by manager.
For instance -
VP
Director
Manager
Worker
Worker
Manager
Worker
Director
Manager
Worker
Worker
Manager
Worker
Worker
Worker

There may in fact be many layers.
The most intuitive solution is some sort of recursive subreport structure, but Access doesn't allow a report to have itself as a subreport.

I can do a subreport for each layer, but since they'll be identical, that seems a waste and a pain to modify later. There must be a slick solution here.

Any help?

Thanks in advance,

Jonathan

Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt

 

[lbigk]

If you can modify your table, you can add a field for Position Level (Director=1, Manager=2, Worker=3). This way, when you create a report all you have to do is group by level first.

 

[annie420]

Sort and group your report so each "layer" has its own header. Ex: sort by Manager, header yes, keep together: whole group

Do the same for each header.

 

[MajP]

I have a couple of different ways I do it, especially if it is purely recursive (where the levele may not be defined or the levels are dynamic).

A couple ideas
1) Use a Treeview and fill it recursively

http://support.microsoft.com/kb/209891

I do not think you can put a tree view on a report (i have never tried it), but you can make the form and print the form.

2) Your problem is a little easier in that it looks like you have a known amount of levels (vp, manager, worker), so you can probably use sorting and grouping.

But what I do is make two fields call "Level" and "nodalSortOrder". I call it nodalSortOrder because it is the order of the nodes in a treeview. I run a recursive function similar to the procedure for loading the tree, but write to the recordset each records "Level" and "nodalSortOrder". This sort order is the same as the order it would appear in a Tree. Ex using your data :

1 VP
2 Director
3 Manager
4 Worker
5 Worker
6 Manager
7 Worker
8 Director
9 Manager
10 Worker
11 Worker
12 Manager
13 Worker
14 Worker
15 Worker

So if I sort my query by nodal sort order everything is correct order except there are not proper indentations.

So I make a table called tblSpaces that has two fields.
"level_FK" and "spaces". Something like

Ex
level_FK spaces
1
2 " -"
3 " +"
4 " *"

now i make a query sorted by my "nodalSortOrder" and link it to my tblSpaces by " level to level_FK" and build a calculated field [spaces] & [FirstName] & [LastName]
Put that field on a report and your data looks like this
Mike Jones
- Mary Black
+ John Brown
* Someone Else
* Another worker

I find that sorting a recursive table in a "nodalSortOrder"
Works well especially when the levels are dynamic. For example you add another layer of management. If interested I have an example.

3)You may also want to Look at this examples of nested queries

http://www.mvps.org/access/queries/qry0023.htm

 

[jflachman]

lbigk and annie420,

I had started down that path and ran into a critical problem. It groups each layer independently. So all VPs, followed by all Directors, then Managers, then Workers.

MajP,
The TreeView won't work because of the quantity of additional information I need to display. However, your second suggestion would work. I did a kluge workaround by doing nested subreports, but they really make summing data to the top level really cumbersome.
Thanks for the help! I really appreciate it.

J

Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt

 

[MajP]

Not sure if you got this done but here is some code to recursively determine the "nodal sort order" and the level. Looks simple but it is actually pretty eloquent.


In my data table I have two fields
lngNodalSort
intLevel

my procedures need
domain 'table or query name
IDName 'the name of the field with the PK
parentIDName ' name of the self referencing FK field
IDType ' a 1 if the keys are strings, 2 if numeric
rootParentID' The very top levels are usually null for their parentID value since they do not report to anyone. But you might have a value of 0 or "none".

Public Enum IDType
  IDString = 1
  IDNumber = 2
End Enum
Public Sub initiateNodalSort(domain As String, IDname As String, parentIDname As String, IDType As Long, rootParentID As Variant)
  'domain: Table or query
  'IDname: Name of the PK field
  'parentIDName: Name of the selfrerencing FK field
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset(domain, dbOpenDynaset)
  'When passing in "parentID" if it is null then specify NULL
  Call NodalSort(rs, rootParentID, IDname, parentIDname, IDType)
End Sub

Public Sub NodalSort(rs As Recordset, parentID As Variant, IDname, parentIDname, IDType As Long, Optional lngSortNumber As Long = 0, Optional intLevel As Integer = 0)
  Dim strCriteria As String
  Dim bk As String
  Dim currentID As Variant
  
  If IsNull(parentID) Then
     strCriteria = parentIDname & " is NULL"
  Else
    If IDType = IDString Then
      strCriteria = parentIDname & " = '" & parentID & "'"
    Else
      strCriteria = parentIDname & " = " & parentID
    End If
  End If
   
  rs.FindFirst (strCriteria)
  'If Not rs.NoMatch Then
     intLevel = intLevel + 1
  'End If
  Do Until rs.NoMatch
   currentID = rs.Fields(IDname)
   rs.Edit
   rs.Fields("lngNodalSortOrder") = lngSortNumber
   rs.Fields("intLevel") = intLevel
   rs.Update
   bk = rs.Bookmark
   lngSortNumber = lngSortNumber + 1
   
   Call NodalSort(rs, currentID, IDname, parentIDname, IDType, lngSortNumber, intLevel)
   rs.Bookmark = bk
   rs.FindNext (strCriteria)
  Loop
  intLevel = intLevel - 1
End Sub

Public Sub testNodalSort()
  Call initiateNodalSort("tblUSMCunits", "strUICcode", "strParentUIC", IDString, Null)
End Sub

 

[galorin]

Just to weigh in with a non-programmatic fix.. Sounds like you need to normalize your schema. You could, for instance have the following tables:

Employee table : containing employee ID name, etc, regardless of position
management table : 2 columns, managerID and employeeID, everyone has an entry here, their ID, and their manager's ID.
Position table : employeeID and management role. Leaves out workers

this makes your joins more complex, but will make your searches faster, as well as preserving the integrity of your data.

Normalizing will also make your report easier, as you can employ grouping levels, based on management levels. Heck, put your tables and query together properly, and the report wizard will do everything for you with this setup.

See

http://en.wikipedia.org/wiki/Database_normalization#Normal_forms

for info regarding normalization, and such. Remember, databases are not glorified spreadsheets.

 

[MichaelRed]

I use a simple "ReportsTo" value for each employee along with their EmployeeId. The Head" of the tree is (arbitrairly) zero. Each employee has their "ReportsTo" value set to the EmployeeId to their supervisor. With the simple logic in place, the reporting can be done in a variety of mechanisims. I believe this was a standard example from Visio (of some long ago version), with the specific intent of generatiing Org. Charts.



MichaelRed

 

[jflachman]

All,

Thanks for the suggestions. I used a recursive subroutine to fill a temporary table with the NodeID, ResourceID and layer. I then join that with the Resource table to pull in a the rest of the data in a query and dump that recordset into the report.

J

Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt

 

[MajP]

Galorin,
Actually the original table structure is better normalized than what you are suggesting. The OP has a normalized structure using a single self referencing table.

Michael,
The OP is doing exactly what you are suggesting. Although he called it Manager ID he states that it is really a ReportToID.

The manager ID is a link to another record in the same table. That person may also have a manager and so on.