Lookup field in same table?

[eagletender]

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.

 

[BigManT]

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 sam
table? For instance, a table of employees, with the SupervisorID as a fiel
so that it would reflect the id of the supervisor, but the supervisor i
also an employee. Or should I create 2 separate tables that would simpl
link the 2 ids

 

[John Vinson]

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!