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

Microsoft Office Access 2003

  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 Button image on the toolbar, or to add a new field at the end of the table, click in the first blank row. Type a name for the field in the Field Name column.

    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. In the first dialog box of the Lookup Wizard, select 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 a Lookup field whose properties are based on the choices you made in the wizard.

ShowIn Datasheet view, create a field that looks up data from another table

  1. In Datasheet view, open the table where you want to add the Lookup field.
  2. Click in the column to the right of where you want to insert the field, and then on the Insert menu, click Lookup Column.
  3. In the first dialog box of the Lookup Wizard, select the option that indicates you want the Lookup field to look up the values in a table or query.
  4. Click Next and follow the directions in the remaining Lookup Wizard dialog boxes.

    Microsoft Access sets properties of the field based on the choices you made in the wizard. By default, the name of the new Lookup field is "Field#," where # is some number. If you want, you can change the name of the field.

ShowIn Design view, create a value list field

  1. Open the table in Design view.
  2. Do one of the following:

    To insert the field within the table, click in the row below where you want to add the field, and then click Insert Rows Button image on the toolbar.

    To add the field to the end of the table, click in the first blank row.

  3. In the Field Name column, type a unique name for the field.
  4. In the Data Type column, click the arrow and select Lookup Wizard.
  5. In the first Lookup Wizard dialog box, click the option that indicates you will type in the values that you want.
  6. Click Next and follow the directions in the remaining Lookup Wizard dialog boxes.

Microsoft Access sets properties of the field based on the choices you made in the wizard.

ShowIn Datasheet view, create a value list field

  1. Open the table in Datasheet view.
  2. Click in the column to the right of where you want to insert the field, and then on the Insert menu, click Lookup Column.
  3. In the first dialog box of the Lookup Wizard, select the option that indicates that you will type in the values that you want.
  4. Click Next and follow the directions in the remaining Lookup Wizard dialog boxes.

    Microsoft Access sets the properties of the field based on the choices you made in the wizard. By default, the name of the new Lookup field is "Field#," where # is some number. If you want, you can change the name of the field.