Want to create a 'lookup' that references the same table

[Jacqui]

I am creating an employee database and for each employee would like to include a 'reports to' field. The 'reports to' field should be a lookup field that references the employee database as the manager would also be contained in the database and creating two sets of data would result in duplicates..

Is there a way to do this

Thanks
Jacqui.

 

[Bob Finster]

I think you want two tables. Employees and Managers.
Assuming the Managers Table is made, in the Employees
Table design, "Reports To" field, in Data Type, select
Lookup Wizard. This will walk you through the steps to set
up the Lookup from the Managers Table.

-----Original Message-----
I am creating an employee database and for each employee

would like to include a 'reports to' field. The 'reports
to' field should be a lookup field that references the
employee database as the manager would also be contained
in the database and creating two sets of data would result
in duplicates...

 

[John Vinson]

I am creating an employee database and for each employee would like to include a 'reports to' field. The 'reports to' field should be a lookup field that references the employee database as the manager would also be contained in the database and creating two sets of data would result in duplicates...

Is there a way to do this?

Well... DON'T.

Table lookup fields are VERY limited, misleading, and unnecessary. See
http://www.mvps.org/access/lookupfields.htm for a critique.

You can certainly create a self-joining key. Just create a field
ManagerID of the same datatype as the EmployeeID and (*using your
Form, not a datasheet!*) use a combo box to enter the supervisor's ID.
This combo box could be based on the Employees table as a whole, or
(if you have a field to indicate that a given employee is a manager) a
query selecting only managerial employees.

 

[John Vinson]

Hi John and Bob,

Thanks for the advice. I'm trying to create a self-joining key but am getting stuck... do either of you know where I can go to get more information on doing this?

I'm not sure what problem you're having.

If the Primary Key of the table is an Autonumber, simply put a Long
Integer field in the table. If the Primary Key is a 12-byte Text
field, put a 12-byte Text field (called ManagerID or whatever you
like).

In the Relationships window add your table TWICE (this may be the
sticking point). Drag the Primary Key of one instance to the ManagerID
field of the other and specify Enforce Relational Integrity.