About creating a field that looks up or lists values in tables (MDB)

The most common Lookup list displays values looked up from a related table. For example, the SupplierID field in the Products table displays this Lookup list:

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 not displayed. 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 the above 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.

Once you've created a Lookup list field, if you add the field to a form, Microsoft Access copies its definition into the form. The combo box or list box is created automatically for the form. However, if you change the definition of a Lookup or value list field in the table after adding it to a form, those changes will not be reflected in that form. To correct this, delete the field from the form and then add it again.

You can create in a table a Lookup field that displays values from the same table. For example, in the Employees table, you can display the name of each employee's supervisor in the ReportsTo field— a Lookup field that displays data from the FirstName and LastName fields by looking up the corresponding EmployeeID.