Creating a field that looks up data from another table in Microsoft Access
1. Open the table in Design view.
2. If the field you want to use as the foreign key for a lookup field already exists, click in the field’s row.
3. In the Data Type column click the arrow and select Lookup Wizard.
4. Click the option ‘I want the lookup column to look up the values in a table query’.
5. Click next and from the subsequent screen select the table or query containing the values you wish to lookup.
6. Click next and from the subsequent screen choose the fields you wish to use for a lookup list.
7. Click next and you will be asked by the lookup wizard to choose a label. Enter a relevant label, i.e. keeping the same as the field name.
8. Click finish. To see the settings of the lookup click on the Lookup tab in the field properties.
Microsoft Access will now create the lookup field and sets the field properties based on the choices you made within the wizard. Once you've added this lookup list you can amend the entries, add to them, and add the field to any form. Access will perform this action automatically by copying its definition into the form. However be aware that if you do change the definition of a lookup, changes will not be reflected in any subsequent form that the lookup is attached to unless you delete the field from the form and add it again.