Having trouble retreiving information from a table 2

[platypus2004]

Ok, I have a table with the following structure:
Name ID ManagerID ManagerID2 ManagerID3
John 5 3 2 1
Pete 4 2 1
Mary 3 2 1
Beth 2 1
Barry 1 1

John reports to Mary.
Pete and Mary report to Beth
Beth reports to Barry
and Barry reports to himself as he is the boss.

I am trying to find a way to enter in "Beth" and get all the people that report under her to be returned.
Meaning, I enter Beth, and the reponse I get is a list containing John, Pete, and Mary.

Any help would be appreciated as I am at a loss.
Thanks

 

[rac2]

One way would be a UNION query.

SELECT Name
FROM myTable
WHERE ManagerID = 2

UNION

SELECT Name
FROM myTable
WHERE ManagerID2 = 2

UNION

SELECT Name
FROM myTable
WHERE ManagerID3 = 2

 

[PHV]

Something like this ?
SELECT A.Name FROM yourTable A, yourTable B
WHERE B.Name='Beth' AND B.ID IN (A.ManagerID,A.ManagerID2,A.ManagerID3);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[OhioSteve]

SQL is not well-suited for this task. I would recommend that you create a vb module to perform the task.

 

[lespaul]

Or create a union query to normalize your data and use that query as the source for your final query.


Leslie

 

[OhioSteve]

Yeah, I didn't say anything about normalizing the data. But lespaul is correct...you need to do that too.

 

[platypus2004]

What do you mean by "normalizing the data"?

 

[OhioSteve]

There are standard rules for creating tables and relationships among tables. You can learn about these rules from a textbook. One of the rules is that you don't duplicate data. Another rule is not to store calculated values. Your table breaks those rules.

As I understand your example, fields like managerID2 and managerID3 show the chain of command for one employee. However, if I told you each employee's name and their supervisor's name, you could infer the chain of command for each employee.

Let me give an example. Let's say that Mary supervises Ed and Bill. John supervises Mary. Your structure would store the fact "John supervises Mary" in the record for Ed and the record for Bill.

For a human, it is much easier to understand the structure in a table like yours. But it will be much easier to work with the relational database if you don't duplicate data.

 

[platypus2004]

Ok, I now have a table with the following structure:
Name ID ManagerID
John 5 3
Pete 4 2
Mary 3 2
Beth 2 1
Barry 1 1

What would be my next step? I want to be able to enter "Beth" into the pop-up text box and have it return Mary, Pete, and John.

 

[lespaul]

now, follow the instructions on creating a union query found in Thread701-658479

leslie

 

[platypus2004]

The union method works wonderfully, although it takes quite a while with several thousand entries in the table. Fortunately, time is not of the essence when running the query.

Thanks for all the help.