Self Join 3 X's

[Guest]

I need to self join a employee table to it's self twice. One will be used to list the managers, the second will be used to list the trainers the third will be used to list the people trained. All of these people are in the employee table. Can this be done? if so how? Thanks!

 

[Michel Walsh]

Hi,


What you describe does not involve JOIN, but "virtual table". In Access
2000 and later:


SELECT whatever
FROM ( SELECT *
FROM myTable
WHERE Job='managers') As Managers
...



In Access 97, you can "save a query" that is the SQL statement inside
the ( ) and use it (cascading your queries) :

SELECT whatever
FROM mySavedQuery ....



To fix the notion, a JOIN will be what "link" exists between the
"virtual" table Managers and the virtual tables Trainers, or "Trainee".



SELECT whatever
FROM trainers INNER JOIN trainees ON trainers.ID = trainees.SupervisorID




Hoping it may help,
Vanderghast, Access MVP

I need to self join a employee table to it's self twice. One will be used

to list the managers, the second will be used to list the trainers the third
will be used to list the people trained. All of these people are in the
employee table. Can this be done? if so how? Thanks!

 

[Allen Browne]

Not really clear what you are doing, but you are storing different kinds of
data here?

It makes sense to put all the people into the Employee table.
Some employees have a role of "manager".
Others have a role of "trainer".
Perhaps some are both trainers and managers?

If so, you need a Role table - one record for "manager", one for "trainer",
and so on for other roles. Then you need an EmployeeRole table with fields
EmployeeID and RoleID. You add a record for each combination, e.g. if
Employee 99 is both a manager and trainer, they have 2 records in this
table.

Training is something else again. You probably need at least these 3:
- Course table: a list of courses or units employees can take.
- CourseInstance table: a particular course offered by a trainer in a
semester (assuming the same course may be offered many times over the
years).
- Enrollee table: the employees enrolled in a course instance.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I need to self join a employee table to it's self twice. One will be used

to list the managers, the second will be used to list the trainers the third
will be used to list the people trained. All of these people are in the
employee table. Can this be done? if so how? Thanks!