Create a field that looks up data from another table in Design view

1. In Design view, open the table where you want to add the lookup field.
   
   
2. Do one of the following:
   • To insert a new field within the table, click in the row below where you want to add the field, and then click Insert Rows on the toolbar, or to add a new field at the end of the table, click in the first blank row. Type the name for the field in the Field Name column, following Microsoft Access object-naming rules.
     
     
   • If the field you want to use as the foreign key for the Lookup field already exists, click in that field's row. 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, click in the SupplierID field's row.
3. In the Data Type column, click the arrow and select Lookup Wizard.
   
   
4. Click the option that indicates you want the Lookup field to look up the values in a table or query.
   
   
5. Click Next and follow the directions in the remaining Lookup Wizard dialog boxes.

   When you click the Finish button, Microsoft Access creates the Lookup field and sets certain field properties based on the choices you made in the wizard. 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. For information on the properties that the Lookup Wizard sets, click .

Note   It is also possible to add a Lookup field to a table that displays values from the same table that contains the Lookup field. For example, in the Employees table of the Northwind sample database the ReportsTo field is a Lookup field that displays data from the FirstName and LastName fields by looking up the corresponding EmployeeID in the same table.