Indirect Lookup Field

[Zachary Turner]

I want to make a Lookup Field based on another Lookup field. In other
words, I have this table A with two fields: ID and Name, where ID is
an Autonumber and Name is a friendly name. Then I have a table B
which links to A via a Lookup Field so that I can select the friendly
name from combo box. Now, I want to have a table C which links to
table B via a lookup field, and only displays the items which are
selected in some combo box of some row of the lookup field from table
A. Granted, I can achieve a similar effect by linking directly to the
source, in this case table A. But now I get a bunch of entries
appearing in the combo box which may be totally irrelevant to my
situation, because the only things that are relevant are those entries
whose text appears in the field in table B. I've tried doing it the
normal way just making a Lookup Field into table B, but what is
displayed are the NUMBERS, not the Text. I think I can do this by
manually changing the Lookup Query, but I don't know what to change it
to because I don't know enough T-SQL.

Thanks

[Larry Linson]

I am convinced that Lookup Fields were one of the worst ideas the Access
team has implemented. They are a violation of good relational database
design practices, they obscure what is really stored, and judging from the
number of questions about them here in the newsgroup, cause more problems
than they solve.

At best, I can see them being useful for a novice or casual end-user who
only works in datasheet view.

I'd say you are confusing functionality that should be implemented, and can
be implemented properly, in Forms with functionality that should be
implemented in Tables.

My advice is eliminate both the Lookup Fields, and use ComboBoxes in Forms
to display the information. You can coordinate the ComboBoxes to limit the
values in the dropdown list of one Combo by the value selected in another.

Or, you can do what you want, easily, in a Query.

Larry Linson
Microsoft Access MVP

"Zachary Turner" <di***********@ gmail.com> wrote in message
news:47******** *************** ***@posting.goo gle.com...

I want to make a Lookup Field based on another Lookup field. In other
words, I have this table A with two fields: ID and Name, where ID is
an Autonumber and Name is a friendly name. Then I have a table B
which links to A via a Lookup Field so that I can select the friendly
name from combo box. Now, I want to have a table C which links to
table B via a lookup field, and only displays the items which are
selected in some combo box of some row of the lookup field from table
A. Granted, I can achieve a similar effect by linking directly to the
source, in this case table A. But now I get a bunch of entries
appearing in the combo box which may be totally irrelevant to my
situation, because the only things that are relevant are those entries
whose text appears in the field in table B. I've tried doing it the
normal way just making a Lookup Field into table B, but what is
displayed are the NUMBERS, not the Text. I think I can do this by
manually changing the Lookup Query, but I don't know what to change it
to because I don't know enough T-SQL.

Thanks