Dear Adam:
I hesitated to start an answer for you, as I may be too busy to get
back to you as much as this question may require. However, as no one
else has tackled it, perhaps there's not much to lose.
Do you have predefined "levels" into which the elements of your
hierarchy are categorized? This is useful as a way to constrain the
data so that "cycles" cannot be created accidentally in the data.
Assuming for the moment that you are "level constrained" does your
tree allow parentage to "skip levels"? That is, is the parent of each
element always an element from the next "higher level" or can it be
more than one level above?
One reason I may be busy is that I'm preparing to author an article on
exactly this topic, the "Level Constrained Hierarchy" but perhaps you
won't want to wait a couple of weeks till this is ready.
It may be premature to ask, but how do you indent to report the
hierarchy? We like to use an "indented outline" format. I really
don't know any other approach that is "human oriented" except to use
Visio and chart it, which is also quite effective but can be
difficult. Any, the Visio approach doesn't really lend itself to most
types of reporting.
So, maybe we can get something started, but I hope you aren't in a
huge hurry.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
