ACC2000: How to Add Lookup Fields in a Microsoft Access Table

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.

Using a Wizard to Create a Lookup Field

1. Start Microsoft Access, and then open the Northwind.mdb sample database.
2. On the View menu, click Database Objects, and then click Tables.
3. Double-click Create table in Design View to create a new table.
4. Type EmployeeLookup in the first row under Field Name.
5. In the Data Type column, click Lookup Wizard.
6. 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.
7. Click the Employees table in the list of tables, and then click Next.
8. Double-click the following fields to add them to the list of selected fields, and then click Next: EmployeeID, LastName, FirstName.
9. Make sure that the Hide key column check box is selected, and then click Next.
10. Click Finish on the last page of the Lookup Wizard.
11. 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.
12. On the View menu, click Datasheet View.
13. 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 Northwind.mdb sample database.
2. On the View menu, click Database Objects, and then click Tables.
3. Click New, and then click Design View 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. Click in the Row Source property box, click the arrow that appears, click Employees in the list, and then click the Build (...) button to open the Query Builder. Click Yes when you are prompted to create a query based on the table
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 determines which field in the row source is saved in the lookup field. In this example, the EmployeeLookup field will store the EmployeeID value from the Employees table, because it is the first field in the row source.
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" if you want to hide a column.
14. On the View menu, click Datasheet View. Click Yes to save the table when you are prompted and name the table TestLookupManually. Click Yes to add a primary key to the table when you are prompted.
    
    Click the drop-down arrow in the EmployeeLookup field and note that there are two columns in the combo box. Note that when you opened the combo box in table TestLookupWizard that it showed the full contents of both the LastName and FirstName columns. When you open the combo box in TestLookupManually, you must use the horizontal scroll bar to view each lookup column. Increasing the width of the EmployeeLookup column enables you to view both lookup columns completely without using the scroll bar.

For more information about creating lookup fields, click Microsoft Access Help on the Help menu, type lookup field in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For additional information about creating lookup fields in Microsoft Access, click the "Creating and Designing Tables" topic on the Microsoft Access 2000 Help page at the following Microsoft Web site: