How to add lookup fields in a Microsoft Access table (304462)


The information in this article applies to:

  • Microsoft Office Access 2003
  • Microsoft Access 2002
This article was previously published under Q304462
Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access database (.mdb).

For a Microsoft Access 2000 version of this article, see 304463.
For a Microsoft Access 97 version of this article, see 304464.

SUMMARY

In Microsoft Access, you can add a field to a table to look up information in another table. You typically use this technique when you want to create relationships between tables. For example, perhaps you have a Products table with a Category field that looks up the category name from a Categories table. By using this technique, you can store the primary key value only in the Categories table, but display the more useful Category name.

This article shows you how to add lookup fields to a Microsoft Access table. First, it shows you how to use a wizard to add the lookup field, and then it shows you how to add the lookup field manually.

MORE INFORMATION

Using a Wizard to Create a Lookup Field

  1. Start Microsoft Access.
  2. On the Help menu, click Sample Databases, and then click Northwind Sample Database. Close the Main Switchboard form when it appears.
  3. On the View menu, click Database Objects, and then click Tables.
  4. Double-click Create table in Design View to create a new table.
  5. Type EmployeeLookup in the first row under Field Name.
  6. In the Data Type column, click Lookup Wizard.
  7. On the first page of the Lookup Wizard, click I want the lookup column to look up the values in a table or query, and then click Next.
  8. Click the Employees table in the list of tables, and then click Next.
  9. Double-click the following fields to add them to the list of selected fields, and then click Next: EmployeeID, LastName, FirstName.
  10. Make sure that the Hide key column check box is selected, and then click Next.
  11. Click Finish on the last page of the Lookup Wizard.
  12. Click Yes to save the table when you are prompted, and name the table TestLookupWizard. Click Yes to add a primary key to the table when you are prompted. The wizard will create a relationship between the new table and the Employees table in the Northwind sample database.
  13. On the View menu, click Datasheet View.
  14. Click the drop-down arrow in the EmployeeLookup field and note that there are two columns in the combo box.

Manually Creating a Lookup Field

  1. Start Microsoft Access.
  2. On the Help menu, click Sample Databases, and then click Northwind Sample Database. Close the Main Switchboard form when it appears.
  3. On the View menu, click Database Objects, and then click Tables.
  4. Double-click Create table in Design View to create a new table.
  5. Type EmployeeLookup in the first row under Field Name.
  6. In the Data Type column, click Number.
  7. Click the Lookup tab under Field Properties.
  8. Change the DisplayControl property to Combo Box.
  9. Click in the RowSource property box, click the arrow that appears, click Employees in the list, and then click the Build (...) button to open the Query Builder.
  10. Double-click the following fields to add them to the query grid: EmployeeID, LastName, FirstName.
  11. On the File menu, click Close to exit the Query Builder. Click Yes when you are prompted to save changes to the SQL statement.
  12. Make sure the BoundColumn property is 1. This property corresponds to the field in the row source that is saved in the field. In this example, the EmployeeLookup field will store the EmployeeID value from the Employees table.
  13. Change the ColumnCount property to 3. This represents the number of fields selected in the row source.
  14. Type 0";1";1" in the ColumnWidths property. This property is a semicolon-delimited list of widths for each column in the row source. Set a column width to 0" if you want to hide the column.
  15. Type 2" in the ListWidth property.
  16. On the View menu, click Datasheet View.
  17. Click Yes to save the table when you are prompted and name the table TestLookupWizard2. Click Yes to add a primary key to the table when you are prompted.
  18. Click the drop-down arrow in the EmployeeLookup field and note that there are two columns in the combo box.
Modification Type:MinorLast Reviewed:6/9/2004
Keywords:kbdesign kbDatabase kbhowto KB304462 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.