Displaying Hierarchical data

[Gerry]

Not sure this belongs in Programming, but I can't figure a way to do it without VBA. I have some thoughts in VBA, but the whole process seems quite convoluted. I'm hoping someone else has a simpler approach.



I have a table with 4 fields, though I think only 3 are necessarily useful. It contains a list of people in the organization, which is hierarchical. The fields are:

• AgentNo (the person's identification number in the agency)
• AgentName (self-explanatory)
• Parent (the AgentNo of the agent who directly manages them)
• Depth (an indicator of how many levels into the hierarchy they are)

Each person has only one record. With the exception of the owner of the agency, every record has a Parent entry. Every Parent entry contains the AgentNo of another person in the table.

I know there are at present 9 levels of depth in this hierarchy, but that is not a constant. As the organization grows, the depth may or may not increase.

I want to produce a report that looks like this:

------------------------------
Bob Smith 123
...John Jameson 456
......Jim Thompson 234
...Susan Storm 098
......Jane Brunson 263
...Brenda Brensen 097
...Bill Stanton 265
------------------------------
(The periods are just used to create spacing - the forum removes my extra spaces.)

You get the idea. Anyone have any good notions on how to get from A (the table) to B (the report)? I'm hoping I'm missing something quite simple.

[maximus]

This is what i assume reading your thread:
This are your fields

• AgentNo (the person's identification number in the agency)
• AgentName (self-explanatory)
• Parent (the AgentNo of the agent who directly manages them)
• Depth (an indicator of how many levels into the hierarchy they are)

Now I guess this is wht you want

AgentName Depth
RuralGuy 5
Pbaldy 4
Drake 3

so you want the name in this order

.....RuralGuy
....Pbaldy
...Drake

I have used dots you want spaces

I have a table with Fields P_Name and hierarchy

I have used the following Functions:

To add spaces before the names:

NameInOrder: Space([hierarchy]) & [P_Name]

To add . or any other characters:

NameInOrder1: String([hierarchy],".") & [P_Name]

I have used the Hierarchy field to provide the number of . or Spaces to be put before the names.

See the screen shot attached to see the effect.

Mark the thread solved if this solves your problem.

[maximus]

Now if you want to use VBA here is a Function i have Done

Option Compare Database
Dim strCharacterAdd As String
Dim intNumber As Integer
Dim strMyName As String
Dim intCounter As Integer
Dim strResultString As String
'*********************************************
'Written by Siddthartha Majumdar on 04/16/2010
'*********************************************
'Scope of the Function adds Characters at the begining of a string
'Number of charecters can be specified
'Blank Space can also be added using " " for strCharacterAdd
'Syntax to use Function Hierarchy(String,Number of Times Character to be added,Character)


Function Hierarchy(strMyName, intNumber, strCharacterAdd) As String
If IsNull(strMyName) Then 'Function is exited if strMyName is Null
Exit Function
Else
strResultString = strMyName
For intCounter = 1 To intNumber 'Loop runs from 1 to intNumber
strResultString = strCharacterAdd & strResultString 'adding the specified character to strMyName
Next
Hierarchy = strResultString
End If
End Function


I have used loop to add the special characters. You can added blank spaces too using this function.


Please mark the thread solved if this solves your problem.

[Gerry]

This is what i assume reading your thread:
This are your fields

• AgentNo (the person's identification number in the agency)
• AgentName (self-explanatory)
• Parent (the AgentNo of the agent who directly manages them)
• Depth (an indicator of how many levels into the hierarchy they are)

Now I guess this is wht you want

AgentName Depth
RuralGuy 5
Pbaldy 4
Drake 3

so you want the name in this order

.....RuralGuy
....Pbaldy
...Drake

I have used dots you want spaces

I have a table with Fields P_Name and hierarchy

I have used the following Functions:

To add spaces before the names:

NameInOrder: Space([hierarchy]) & [P_Name]

To add . or any other characters:

NameInOrder1: String([hierarchy],".") & [P_Name]

I have used the Hierarchy field to provide the number of . or Spaces to be put before the names.

See the screen shot attached to see the effect.

Mark the thread solved if this solves your problem.

It's not just a matter of adding spaces - I need to preserve the hierarchical structure - showing each person listed under their manager. That's the information in the [Parent] field.

To be clearer, I've created the records that would result in the example in my prior post:

AgentNo...AgentName.......Parent......Depth
123.......Bob Smith.................. 1
456.......John Jameson....123.........2
234.......Jim Thompson....456.........3
098.......Susan Storm.....123.........2
263.......Jane Brunson....098.........3
097.......Brenda Brensen..123.........2
265.......Bill Stanton....123.........2

(Again, I've used periods to create proper spacing, so you can see the table fields more clearly.)
I've put them in the order they were in the report sample, though they could be in any order in the table. Essentially, I'd want all the level-1 records, each with their corresponding level-2 records under them, each with their corresponding level-3 records under them, etc. The [Depth] field is probably only useful if I use it as some sort of cue for formatting in the report. The hierarchy is all contained in the link between [AgentNo] and [Parent]

[maximus]

then make a select query and arrange the information ORDER BY hierarchy. This will solve your problem.

[Gerry]

Ordering on [Parent] doesn't put them in hierarchical order. If you look back at the sample, Jane Brunson would be listed up top (lowest [Parent] number), though she's 3 levels into the hierarchy.

Ordering by [Depth] would have Jane Brunson and Jim Johnson listed last, but not under their respective managers.

Combining these two, I'd just end up with them clustered by level, rather than in descending hierarchy levels.

[maximus]

Yes now looking at the sample that you have provided the I have fully understood your problem now. Well in my misunderstanding I had created a function to add spaces and characters before strings ha!ha!. your problem is on my mind.

[Gerry]

Definitely not your fault. I went back and posted a better explanation, because I don't see how you could have possibly gotten the right idea from my first post.

[pbaldy]

You either want something recursive or like this:

http://allenbrowne.com/ser-06.html

Allen's solution would work but requires you to know how many levels. If memory serves, later versions of SQL Server have some built-in capabilities; Access does not. It seems like I did a recursive sample at some point, but I can't find it right off. If you can provide a sample db with enough data to be meaningful, one of us might be able to cobble together a solution.

[maximus]

Thank you Sir for the Link. Its late here almost midnight. Will try again tomorrow

[Gerry]

You either want something recursive or like this:

http://allenbrowne.com/ser-06.html

Allen's solution would work but requires you to know how many levels. If memory serves, later versions of SQL Server have some built-in capabilities; Access does not. It seems like I did a recursive sample at some point, but I can't find it right off. If you can provide a sample db with enough data to be meaningful, one of us might be able to cobble together a solution.

This might work as a stop-gap for the beta version of the database, but I can't rely on it in the production version. There's no way to know how many levels there might be in a year, and it would be difficult for the user to spot the error if there were too many levels for the query.

Do you have a good link where I can research a recursive solution? I'm pretty rusty, but once I wrap my mind around it, I can program it.