I am kinda new at this whole ASP thing, but here is what I
am trying to do. I have an access database that is laid
out as follows:
ID Name Supervisor
1 Mike 0
2 Dave 1
3 Gary 1
4 Sally 2
5 Mary 2
6 Susan 3
7 Ben 3
Here is what I am trying to accomplish:
If I enter an ID number, I would like to view all of the
names that have that ID as a Supervisor and all of the
names below that.
For example, if I enter 3 (Gary), I would get Susan and
Ben. However, if I enter 1 (Mike), I would get Dave,
then Sally and Mary; and get Gary then Susan and Ben.
Do you have any advice for me from an ASP and SQL
point of view? Thanks for your help!
Heh, we've talked about this before, I believe. What you're trying to do is
fully traverse a tree structure that's "hidden" in the database table. Here
is a link to the last question that dealt with this topic:
However, you're somewhat limited because you're using MS Access as your
database. Access has a much less robust SQL language than, say, SQL Server,
so you're pretty limited in what you can do. How about:
1.) Calculate and store each person's "depth" in the tree. In other words,
big boss man, who reports to no one (except possibly the stockholders :) has
a depth of 0, Prez has a depth of 1, etc. Be careful, you'll need to
recalculate as the data changes.
2.) When performing this query, pull back every employee below the queried
employee's depth.
3.) Write some magic ASP code to make the results pretty. (Sorry, can't help
ya much with this one, but you're basically looping through the recordset a
bunch to traverse the tree.)
