linking 2 fields to 1 table

[fcinelli]

Hi,

I have a database with the following:

TABLE POSITION
* positionID
positionTitle

TABLE JOB
*positionID
*HomeIncumbent --> supposed to relate employeeID
*CurrentEmployee --> supposed to relate to employeeID

TABLE EMPLOYEE
*employeeID
Name

Note that the HomeIncumbent and CurrentEmployee are supposed to link
to the employee table. So we can have employee ID 2 as home and ID 4
as current for one record in the Job table. And another might be
employee ID 4 as home and ID 8 as current.

What would I have to do so that when I run a query, I can show the
employee name that corresponds to the employee ID for both home and
current for a given job. (ie. show the position title, name of the
home incumbent and the name of the current employee) Do I have to
change my table design?

Any help would be appreciated. I cannot think of any way to solve
this.

 

[John W. Vinson]

What would I have to do so that when I run a query, I can show the
employee name that corresponds to the employee ID for both home and
current for a given job. (ie. show the position title, name of the
home incumbent and the name of the current employee) Do I have to
change my table design?

Your table design is fine. All you need to do is to create a query by
adding the jobs table once, and the employee table TWICE. Join one
instance of the employee table to each of the two linking fields.


John W. Vinson [MVP]

 

[fcinelli]

Your table design is fine. All you need to do is to create a query by
adding the jobs table once, and the employee table TWICE. Join one
instance of the employee table to each of the two linking fields.

John W. Vinson [MVP]

Thanks John, you're absolutely right. I tried that before but I put
the job table twice instead of the employee table which clearly does
not make any sense.