Lookup field in same table?

Can you create a field that will look up the id of a record in the same
table? For instance, a table of employees, with the SupervisorID as a field
so that it would reflect the id of the supervisor, but the supervisor is
also an employee. Or should I create 2 separate tables that would simply
link the 2 ids.

Eagle, no you cannot create a field that will lookup the id of a record in the same table, but you can accomplish this in a query which looks at the table. Use the DLoopup function. See Access help for how to set it up. Most people would probably have one table with a supervisor id field and then add that table twice to the same query and do a self join between the employee id field and the supervisor id field in order to see who is whose supervisor. For the man at the top, I suppose you could put his own employee id in his supervisor id field to show that he manages himself.

----- eagletender wrote: -----

Can you create a field that will look up the id of a record in the same
table? For instance, a table of employees, with the SupervisorID as a field
so that it would reflect the id of the supervisor, but the supervisor is
also an employee. Or should I create 2 separate tables that would simply
link the 2 ids.

On Fri, 14 May 2004 14:17:22 -0600, "eagletender"
wrote:

Can you create a field that will look up the id of a record in the same
table? For instance, a table of employees, with the SupervisorID as a field
so that it would reflect the id of the supervisor, but the supervisor is
also an employee. Or should I create 2 separate tables that would simply
link the 2 ids.


You can create a "self join query" - but I would STRONGLY recommend
that you not use a "self join lookup" in a table, or for that matter
ANY Lookup Field in ANY table. See
http://www.mvps.org/access/lookupfields.htm for a critique of this
misfeature!

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public