database design - Look-up vs relationship Microsoft Access

Question

I'm developing a Microsoft Access 2013 based information system. One of the client's demands was to simplify the data entry process by using combo box with available values.

For example, instead of entering agentID the client asked to let the user choose agent name from the combo box, the same logic with other similar fields.

In brief:
I need to avoid as much as possible the need to enter the values ID and let to user choose them from the combo box.

Microsoft Access has a built-in lookup wizard that allows to user to bind the table field with specific field from another table, e.g. to link cityID from tblVoyage with tblCities/cityID by lookup wizard, and let user choose the city from the combo box and not by typing specific city ID into the field.

Everything looks great, but there one confusing moment. During DB course I learnt, that in order to build a database and work with it, we have to define relationships between the tables (1:1, 1:M, M:N), but if I do it I can't use lookup wizard, because I've already defined relationships between tables. And, as a result, the user has to type all IDs manually, instead of choose them from the combo box.

So, I want:

1. To understand when exactly should I use Access lookup wizard and when define a relationship between the tables.
2. How to correctly minimize the number of times when the user has to type the data instead of choose the wanted item from the combo box.

Answer 1

The general consensus here seems to be that Lookup fields should be avoided. They are really just a short-cut to having a "proper" lookup table and they hide what is really going on at the table level. For example, say you have a Lookup field for [Size] with values 'Small', 'Medium' and 'Large'. When you look at the table you see the words, but the chances are good that the table really contains numbers like 1, 2, and 3. You go to

UPDATE tblName SET Size="Large"

and the query fails because it what you actually need to do is

UPDATE tblName SET Size=3

(When you maintain a "proper" separate lookup table your field in the main table shows what is actually in the field: the PK value for the selected item in the lookup table. You can still design your forms to have a combo box populated by the lookup table and bound to a field in the main table.)

For even more confusion, the Lookup wizard offers an "allow multiple selections" option so a field can hold more than one value (sort of). That was apparently added so Access could work better with SharePoint, and that is really the only time when that feature should be used. In the overwhelming majority of other cases it's better to explicitly maintain a separate child table to store the multiple selections.

Edit

The Combo Box Wizard will guide you through the process of linking your combo box to its record source (the lookup table) and binding its value to a field in your data table. For example, say you have already set up a lookup table for [Agents]

ID  AgentName
--  ---------
 1  Gord     
 2  Angie    

...and a data table for [Accounts]

ID  agentID  AccountName
--  -------  -----------

You create a new Form with the [Accounts] table as its Record Source. When you go to add a Combo Box to the form the wizard should run and ask you "How do you want your combo box to get its values?". You choose "I want the combo box to get the values from another table or query."

In the next step you choose the [Agents] table:

Next you tell the wizard that you want to display the [AgentName]:

After you choose a sort order (if desired) you get to confirm the column width(s). Leave "Hide key column (recommended)" enabled.

Finally, you get to choose what happens to the check box's value. This is where you "bind" it to the [agentID] field in the [Accounts] table:

Note that the combo box will display the [agentName] for the user to select, but its .Value will be the numeric [Agents].[ID], and that is what will be stored in [Accounts].[agentID].