The most common Lookup list displays values looked up from a related table. For example, the SupplierID field in the Products table of the Northwind sample database displays this Lookup list:
This list is created by looking up the SupplierID values in the Suppliers table and displaying the corresponding Supplier names. Picking a value from a Lookup list sets the foreign key value in the current record (SupplierID in the Products table) to the primary key value of the corresponding record in the related table (SupplierID in the Suppliers table). This creates an association to the related table to display (but not store) the Supplier names in the record. The foreign key (SupplierID) is stored but doesn't display. For this reason, any updates made to the data in the Suppliers table will be reflected in both the list and records in the Products table. You must define a Lookup list field from the table that will contain the foreign key and display the Lookup list. In this example, the Lookup list field would be defined from the Products table.
A value list looks the same as a Lookup list, but consists of a fixed set of values you type in when you create it. A value list should only be used for values that will not change very often and don't need to be stored in a table. For example, a list for a Salutation field containing Mr., Mrs., or Ms. would be a good candidate for a value list. Choosing a value from a value list will store that value in the record — it doesn't create an association to a related table. For this reason, if you change any of the original values in the value list later, they will not be reflected in records added before this change was made.
You can add a new Lookup or value list field in either table Design view or table Datasheet view. However, if the field you want to use as the foreign key for a Lookup field already exists, you must open that field's table in Design view to define the Lookup field. For example, if you have a Products table that has a SupplierID field already defined, and you want to change it to a Lookup field to display supplier names from your Suppliers table, you must open the Products table in Design view to change SupplierID to a Lookup field.
Return to Create a field that looks up or lists values in tables