Desired Result Table for a
selected Table tblA ID of 1917
Here's what's going on: Table tblA is a list of people (the ID is unique). Table tblB is a list of reporting relationships among the people in Table tblA. The Level column indicates how far down the org tree they are. So, for example, Ed (1919) reports to Bill (1917) who reports to Al (1916). In table tblB, record 1 shows Bill reporting to Al. Record 3 shows Ed reporting to Bill. And record 7 shows Ed reporting to Al 2 levels up (ie. through Bill).
The result that I'm trying to generate is all of the people that a selected person could possibly report to. So, for example, if I selected Bill (1917) from Table tblA, the only people he could report to would be Al, Carrie and Mike (since everyone else in Table tblA either reports to Bill at some level or is Bill).
I've tried (unsuccessfully):
"SELECT DISTINCT tblA.ID, tblA.Name From tblA LEFT OUTER JOIN tblB ON tblB.StaffID = tblA.ID WHERE tblB.BossID <> " & selID
where selID is the ID of the person of interest (1917 in the example above).
Please help me find the right syntax to get the desired resulting table.
I am unable to find a reference to 1923 (Mike) in table b. Do you want to know who 1917 supervises or who 1917 is supervised by ? What is the relationship between bossid and staffid - how does 1917 relate to 1918 and 1923 ?
Table tblB is a list of reporting relationships among the people listed in tblA. Mike doesn't report to anyone, and he doesn't have anyone reporting to him. That's why he doesn't appear in tblB. But since he doesn't report to Bill at any level, the user may want to establish a new reporting relationship having Bill report to Mike. That's why Mike should be included in the result table.
The end user will be looking at a personnel record from tblA (eg, Bill) wanting to assign a new "boss" to that person. I'm trying ot give him a combobox list (the Result Table) of possible bosses. Frank, for example, shouldn't be in that list of Bill's possible bosses because Bill is Frank's boss (4th record, tblB). Kristin can't be Bill's boss either, since Kristin reports to Bill through Frank (5th and 9th records, tblB).
Also, there is no reporting relationship between 1917 and either 1918 or 1923. That's why 1918 and 1923 should be included in the Result Table for 1917. As it happens, 1916 is already Bill's boss (record 1, tblB), so 1916 is also a candidate boss for Bill.
Here's how the example organization chart would look:
| ----1919 Ed
| ----1920 Frank
| ----1921 Kristin
| ----1922 Laura
I hope this is clearer. Thanks again for taking a look at my question.