I'm in the process of setting up a new table and I designated one field to be a lookup using the wizard. I named the field 'business' to match the field name in the customer table and told the wizard to use that particular table and field to 'lookup'. After I clicked Finish, it changed the field name to 'cust id' (in the new table) and told me I already had a field by that name, which is true. In the new table, I had a 'cust id' field and a 'business' field (which was to be the lookup field). What happened here and where did I go wrong? Maybe I don't fully understand the 'behind the scenes' on this lookup thing?! Thanks!
Most of use will recommend that you NEVER use Microsoft's misdesigned,
misleading, and all but useless Lookup Wizard AT ALL. See
http://www.mvps.org/access/lookupfields.htm for a critique.
It does make it a bit easier to use table datasheets for editing data,
up until you try something the least bit more complicated (such as
what you're trying to do) - then it fails. But table datasheets are
NOT good places to do any data entry or editing, they're much too
limited. Instead, use Forms; you can put Combo Boxes ("lookups") on a
Form perfectly easily, with or without having used the lookup wizard.
You should also be aware that a Lookup field "business" DOES NOT
CONTAIN the business name. It contains a CustID, which is concealed
from your view by the lookup. You do not need to - and should not -
have fields in your new table redundantly storing the business name;
if you have a Form, just use a combo box to display it, or base the
Form on a Query linking the two tables.
