Employee Supervisor relationship

I want to create a relationship for a supervisor over an employee.
I have a table (Person) with PersonID as the PK and CompanyID_FK as the FK
to CompanyID in the table Company.
In the Relationships window I created a copy of the table Person to get
Person_1.
I have Person.SupervisorID joined to Person_1.PersonID (where the join type
is where they are both equal).
I have tried various ways of creating a combo box in a form (main form
Frm_Supervisor and subform Subform_Supervisor) so that only names of people
in that company are listed. I have added a combo box linked to SupervisorID
with the following;
SELECT Person.Person_ID, Person.[First Name], Person.[Last Name],
Person.CompanyID_FK FROM Person WHERE
(((Person.CompanyID_FK)=Forms!Frm_Supervisor!Compa nyID));
I have two problems (1) I get a pop up asking for a parameter value and (2)
it seems to get stuck on one company.
I also tried WHERE "&Me.CompanyID" but that did not work either.

Any help appreciated.

I believe you need a SupervisorID field in the Person table...so that each
employee record has a SupervisorID value.


--
NTC


"Graeme at Raptup" wrote:

I want to create a relationship for a supervisor over an employee.
I have a table (Person) with PersonID as the PK and CompanyID_FK as the FK
to CompanyID in the table Company.
In the Relationships window I created a copy of the table Person to get
Person_1.
I have Person.SupervisorID joined to Person_1.PersonID (where the join type
is where they are both equal).
I have tried various ways of creating a combo box in a form (main form
Frm_Supervisor and subform Subform_Supervisor) so that only names of people
in that company are listed. I have added a combo box linked to SupervisorID
with the following;
SELECT Person.Person_ID, Person.[First Name], Person.[Last Name],
Person.CompanyID_FK FROM Person WHERE
(((Person.CompanyID_FK)=Forms!Frm_Supervisor!Compa nyID));
I have two problems (1) I get a pop up asking for a parameter value and (2)
it seems to get stuck on one company.
I also tried WHERE "&Me.CompanyID" but that did not work either.

Any help appreciated.