ISA Hierarchy - Count (1 Viewer)

[yippie_ky_yay]

Hello everyone,

I'm a little stumped on something. I would like to know how many levels down something is in a hierachy.

Table_Hierarchy
ParentID
ChildID

Here are some typical entries (1 for President, 2 for Manager, 3 for Secretary)
1 2
2 3


If you follow this, 3 is a child to 2 which in turn is a child to 1. I would like to be able to know that there are two levels above 3 (my current hierarchy varies from 1 to about 8 levels!) Could someone help with this please? My brain just won't process this one!

Thanks in advance,
-Sean

 

[dcx693]

Just curious....why are you looking to do something like this?

 

[WayneRyan]

yky,

Just for grins, take a look at how MS Project store this. In
one task table, they have fields for UniqueID, ParentID and
OutlineLevel. One table, no relational db concepts.

If its good enough for MS ...

Wayne

 

[yippie_ky_yay]

dcx693 - I thought of this one day when I was creating a secure Web site. I have users assigned to different levels - what shows up on the site, and what you can do with it depends on your level. I then started thinking that it would be nice if I could create something like what they do in applications like Access - that is, to have users assigned to groups and properties of that group would be inherited. Another benefit would be if I could have groups inherit from other groups.

So far it works, my problem is illustrated when I try to present the hierarchy in some way - like this:
Admin
--PowerUser
----SubPowerUserLvl1
----SubPowerUserLvl2
--GuestUser


Wayne - I've never used or even seen MS Project but I will try to find someone here who uses it. Thanks for the tip!

-Sean

 

[dcx693]

Would you be assigning the users the actual numbers you mentioned? Or just placing them relative to each other (meaning that you'll make one user above or below another in the hierarchy) and letting Access assign position numbers for you?

 

[yippie_ky_yay]

Yes I am currently assigning each user a number which represents a group (1 through 8). Using the table in my original post, I would assign newuser the number "3" for the Secretary level.

Doing that works with the site perfectly. I only got stumped when I tried to present the hierarchy in an easy to read format.
ie:
President
-- Manager
-- -- Secretary

In order for me to display something like above, I would need to know that there are 2 levels above Secretary and 1 level above Manager in order to tell the server how many times to print "-- " before the group name.

Sound like it can be done?

-Sean