ACC97: How to Add Lookup Fields in a Microsoft Access Table (304464)


The information in this article applies to:

  • Microsoft Access 97
This article was previously published under Q304464
Novice: Requires knowledge of the user interface on single-user computers.

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

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, and then open the sample database Northwind.mdb.
  2. On the View menu, click Database Objects, and then click Tables.
  3. Click New to create a new table.
  4. Click Design View, and then click OK.
  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 the Hide key column check box is selected, and then click Next.
  11. On the last page of the Lookup Wizard, click Finish.
  12. When you are prompted to save the table, click Yes, and name the table TestLookupWizard.
  13. When you are prompted to add a primary key to the table, click Yes. The wizard will create a relationship between the new table and the Employees table in the Northwind sample database.
  14. On the View menu, click Datasheet View.
  15. 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, and then open the sample database Northwind.mdb.
  2. On the View menu, click Database Objects, and then click Tables.
  3. Click New to create a new table.
  4. Type EmployeeLookup in the first row under Field Name.
  5. In the Data Type column, click Number.
  6. Click the Lookup tab under Field Properties.
  7. Change the Display Control property to Combo Box.
  8. In the Row Source property, click Employees in the list, and then click the Build (...) button to open the Query Builder.
  9. Double-click the following fields to add them to the query grid: EmployeeID, LastName, FirstName.
  10. On the File menu, click Close to exit the Query Builder. Click Yes when you are prompted to save changes to the SQL Statement.
  11. 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.
  12. Change the ColumnCount property to 3. This represents the number of fields selected in the row source.
  13. 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" to hide the column.
  14. Type 2" in the ListWidth property.
  15. On the View menu, click Datasheet View.
  16. When you are prompted to save the table, click Yes, and then name the table TestLookupWizard2.
  17. When you are prompted to add a primary key to the table, click Yes.
  18. Click the drop-down arrow in the EmployeeLookup field, and note that there are two columns in the combo box.

REFERENCES

For more information about creating lookup fields, click Microsoft Access Help on the Help menu, type lookup fields in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
Modification Type:MajorLast Reviewed:9/26/2003
Keywords:kbhowto KB304464
©2003 Microsoft Corporation. All rights reserved.