Re: multilevel hierarchy query

From: baphensley <baphensley_at_usa.net>
Date: 16 Feb 2005 10:17:22 -0800
Message-ID: <1108577842.173931.131800@f14g2000cwb.googlegroups.com>

David,

Thanks. That was an interesting read.

Unfortunately, I've inherited the tables and can't change them, just read them.

However, I can estimate the maximum number of levels in the tree. With this in mind, I tried a brute force approach. This seems to get all the staff below a manager (as long as they're no more than 6 levels deep). Not elegant, but it seems to be effective.

SELECT tblStaff.StaffID, [tblStaff]![StaffID] & " " &

[tblStaff]![ReportsToID] & " " & [B2]![ReportsToID] & " " &
[B3]![ReportsToID] & " " & [B4]![ReportsToID] & " " &
[B5]![ReportsToID] AS ChainOCmd

FROM ((((tblStaff LEFT JOIN tblStaff AS B1 ON tblStaff.ReportsToID = B1.StaffID) LEFT JOIN tblStaff AS B2 ON B1.ReportsToID = B2.StaffID) LEFT JOIN tblStaff AS B3 ON B2.ReportsToID = B3.StaffID) LEFT JOIN tblStaff AS B4 ON B3.ReportsToID = B4.StaffID) LEFT JOIN tblStaff AS B5 ON B4.ReportsToID = B5.StaffID
WHERE ((([tblStaff]![StaffID] & " " & [tblStaff]![ReportsToID] & " " &
[B2]![ReportsToID] & " " & [B3]![ReportsToID] & " " &
[B4]![ReportsToID] & " " & [B5]![ReportsToID]) Like "*" & [Boss: ] &
"*"));

Thanks,
Bruce Received on Wed Feb 16 2005 - 12:17:22 CST