Lookup Typed-In Values

[Mae]

I went to the link referenced in a question below,
regarding the evils of using Lookup. Does that apply only
to looking up values from other tables, or to looking up
values that you've manually typed in as well? I have
several fields that are formatted as Lookups with values
that I entered in order to restrict possible options (and
make data entry easier and eliminate spelling errors,
etc). I don't see the disadvantage there.

Also, how do you get a foreign key, if not by looking it
up from another table? For instance, I have
tblContactInfo, TblCardInfo, and TblCardAssignments.
TblCardAssignments looks up ContactID from TblContactInfo
and CardID from TblCardInfo, which creates a record
showing what user has what card. ...Have I totally
misunderstood how to create a junction table??

I'm really confused, and appreciate whatever insights
anyone can provide.

 

[Douglas J. Steele]

Since you should never be working with the tables themselves, but should
always be using forms, there should be no need to use Lookup fields for
anything. You create a combobox control on the form to get the functionality
you want (i.e.: to restrict options and save typing)

Yes, you need to lookup values in the other table. You do this using a
query.

Assuming tblCardAssignments has fields ContactID and CardID, and you want to
show ContactNm and CardNm, your query would probably look something like:

SELECT tblCardAssignments.ContactId, tblContactInfo.ContactNm,
tblCardAssignments.CardID, tblCardInfo.CardNm
FROM tblCardAssignments INNER JOIN (tblContact INNER JOIN tblCardInfo
ON tblCardInfo.CardId = tblCardAssignment.CardId)
ON tblContact.ContactId = tblCardAssignments.ContactId

To build this query through the GUI, you'd select all 3 tables, make sure
the relationship lines are properly drawn to connect them, then drag the
appropriate fields into the grid.

The reason we're so against using the lookup fields is that people tend to
think that the field contains the text value, when in fact it only contains
the ID. That's misleading.