Microsoft KB Archive/304462

Knowledge Base

How to add lookup fields in a Microsoft Access table

Article ID: 304462

Article Last Modified on 3/29/2007

---

APPLIES TO

• Microsoft Office Access 2007
• Microsoft Office Access 2003
• Microsoft Access 2002 Standard Edition

---

This article was previously published under Q304462

Novice: Requires knowledge of the user interface on single-user computers.

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

Use a wizard to create a lookup field

1. Start Access.
2. On the Help menu, click Sample Databases, and then click Northwind Sample Database. Close the Main Switchboard form when it appears.
   

Note In Access 2007, click Sample in the Template Categories pane, click Northwind 2007, and then click Download.

1. On the View menu, click Database Objects, and then click Tables.
   

Note In Access 2007, in the Tables group on the Create tab, click Table Design.

1. Double-click Create table in Design View to create a new table.
   

Note In Access 2007, skip this step.

1. Type EmployeeLookup in the first row under Field Name.
2. In the Data Type column, select Lookup Wizard.
3. On the first page of the Lookup Wizard, click to select the I want the lookup column to look up the values in a table or query check box, and then click Next.
4. Click the Employees table in the list of tables, and then click Next.
5. Double-click the EmployeeID field, the LastName field, and the FirstName field to add the fields to the list of selected fields, and then click Next two times.
6. Make sure that the Hide key column check box is selected, and then click Next.
7. Click Finish on the last page of the Lookup Wizard.
8. Click Yes to save the table when you are prompted, and then name the table TestLookupWizard.
9. Click Yes to add a primary key to the table when you are prompted.
   

Notice that the wizard will create a relationship between the new table and the Employees table in the Northwind sample database.

1. On the View menu, click Datasheet View.
   

Note In Access 2007, open the table in the Datasheet View.

1. Click the drop-down arrow in the EmployeeLookup field.
   

Notice that two columns appear in the combo box.

Manually create a lookup field

1. Start Access.
2. On the Help menu, click Sample Databases, and then click Northwind Sample Database. Close the Main Switchboard form when it appears.
   

Note In Access 2007, click Sample in the Template Categories pane, click Northwind 2007, and then click Download.

1. On the View menu, click Database Objects, and then click Tables.
   

Note In Access 2007, in the Tables group on the Create tab, click Table Design.

1. Double-click Create table in Design View to create a new table.
   

Note In Access 2007, skip this step.

1. Type EmployeeLookup in the first row under Field Name.
2. In the Data Type column, click Number.
3. In the Field Properties window, click the Lookup tab.
4. Iin the DisplayControl list, click Combo Box.
5. Click in the RowSource property box, click the arrow that appears, click Employees in the list, and then click Build to open the Query Builder.
6. Double-click the EmployeeID field, the LastName field, and the FirstName field to add the fields to the query grid.
7. In the Close group on the Design tab, click Close to exit Query Builder.
8. Click Yes when you are prompted to save changes to the SQL statement.
9. Make sure that the BoundColumn property is set to 1.
   

Note This property corresponds to the field in the row source that is saved in the field. In this example, the EmployeeLookup field stores the EmployeeID value from the Employees table.

1. Change the value of the ColumnCount property to 3.
   

Note This value represents the number of fields that are selected in the row source.

1. Type 0";1";1" in the ColumnWidths box.
   

Note The ColumnWidths 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.

1. Type 2" in the ListWidth box.
2. On the View menu, click Datasheet View.
   

Note In Access 2007, open the table in the Datasheet View.

1. Click Yes to save the table when you are prompted, and then name the table TestLookupWizard2.
2. Click Yes to add a primary key to the table when you are prompted.
3. Click the drop-down arrow in the EmployeeLookup field.
   

Notice that two columns appear in the combo box.

Additional query words: OfficeKBHowTo inf Acc2002 Acc2003 ACC2007

Keywords: kbinfo kbexpertisebeginner kbdesign kbdatabase kbhowto KB304462

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.