Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations MikeeOK on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Having trouble retreiving information from a table 2

Status
Not open for further replies.

platypus2004

Technical User
Sep 21, 2004
10
US
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
 
One way would be a UNION query.
Code:
SELECT Name
FROM myTable
WHERE ManagerID = 2

UNION

SELECT Name
FROM myTable
WHERE ManagerID2 = 2

UNION

SELECT Name
FROM myTable
WHERE ManagerID3 = 2
 
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
 
SQL is not well-suited for this task. I would recommend that you create a vb module to perform the task.
 
Or create a union query to normalize your data and use that query as the source for your final query.


Leslie
 
Yeah, I didn't say anything about normalizing the data. But lespaul is correct...you need to do that too.
 
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.
 
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.
 
now, follow the instructions on creating a union query found in Thread701-658479

leslie
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top