general report question on hierarchy

[JohnE]

I have an application that works on a hierarchy (treeview) method. There is
a table that all the info is in. A field in the table is called IsChildOf
and another that indicates IsTopLevel. There can be multiple levels under
the top level or no levels under the top level.

I can see it coming that the users will want to see a hierarchy style report
from the app. So, I am wondering if anyone out there has ever done such a
report? If so, how? I have no idea where or how to begin something like
that. Any thoughts, suggestions, idea, and/or recommendations are
appreciated.

Thanks.

John

 

[Allen Browne]

This is not easy in JET, John.

How many generations do you need to go back? If 3 is enough, you may be able
to do it like this:
http://allenbrowne.com/ser-06.html

Beyond that, it starts to get more involved. One of the problems is the
possibility of entering a record as its own grandparent (or further
removed), in which case you have an infinite loop that can never be fully
resolved.

I'm not sure I would have the IsTopLevel: a null IsChildOf would indicate
that (for practical purposes) this record would be treated as being top
level. Other than that, the self-join is a wonderfully flexible design, even
if it is frustrating when you are ask to fully resolve it back through all
generations.

 

[JohnE]

Mr Browne, thanks for the info. So far, several levels is all I've seen.
Hopefully they don't get to carried away on it. The IsTopLevel had to be put
in. All requests go into the table. They stay there until it is reviewed
and prioritized or removed. At first the empty IsChildOf was considered top
level but after having hundreds show up as top level, the other field was
added.

Again, thanks for the info.
....John

 

[Allen Browne]

Okay: I understand that you may need to assign a record as being a top level
one, as distinct from one where the parent is unknown but it's not top
level.