Creating a field to look up data in the same table

[johnsosh]

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.

 

[John W. Vinson]

I'd like to create a look-up field in a table

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.

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]

 

[johnsosh]

Thank you very much. I'll try that.

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]

 

[Jamie Collins]

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.

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

Jamie.

--