Creating a field to look up data in the same table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'd like to create a look-up field in a table that uses data from the same
table. According to Access 2003 Help, this is possible, but no instructions
are given for how to accomplish this. When I try doing it the same way I
create look-up fields that use data in other tables I don't have the option
of using the current table's data. Thanks for any help that can be provided.
 
I'd like to create a look-up field in a table
Click to expand...

Well... most of us would say that you Don't.

See http://www.mvps.org/access/lookupfields.htm for a critique of this Access
misfeature.

If you're attempting to edit data in the Table, DON'T. Table datasheets are of
VERY limited utility, and should be used only for debugging. Use a Form for
interaction with your data.
that uses data from the same
table. According to Access 2003 Help, this is possible, but no instructions
are given for how to accomplish this. When I try doing it the same way I
create look-up fields that use data in other tables I don't have the option
of using the current table's data. Thanks for any help that can be provided.
Click to expand...

You can create a Combo Box (on a form!) based on any table, including the
table upon which the form is based. For instance, if you want to model an
employee-supervisor relationship, your table would have an EmployeeID as the
primary key, and a SupervisorID field as a self-join foreign key. On the Form
you would have a combo box bound to SupervisorID; its RowSource would be a
query selecting all of the supervisors from the employee table.

John W. Vinson [MVP]
 
Thank you very much. I'll try that.

John W. Vinson said:
Well... most of us would say that you Don't.

See http://www.mvps.org/access/lookupfields.htm for a critique of this Access
misfeature.

If you're attempting to edit data in the Table, DON'T. Table datasheets are of
VERY limited utility, and should be used only for debugging. Use a Form for
interaction with your data.


You can create a Combo Box (on a form!) based on any table, including the
table upon which the form is based. For instance, if you want to model an
employee-supervisor relationship, your table would have an EmployeeID as the
primary key, and a SupervisorID field as a self-join foreign key. On the Form
you would have a combo box bound to SupervisorID; its RowSource would be a
query selecting all of the supervisors from the employee table.

John W. Vinson [MVP]
Click to expand...
 
For instance, if you want to model an
employee-supervisor relationship, your table would have an EmployeeID as the
primary key, and a SupervisorID field as a self-join foreign key.
Click to expand...

Only if using a denormalized adjacency list model; other models are
available.

Jamie.

--
 
Back
Top