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.
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.
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.
Double-click Create table in Design View to create a new table.
Note In Access 2007, skip this step.
Type EmployeeLookup in the first row
under Field Name.
In the Data Type column, select Lookup Wizard.
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.
Click the Employees table in the list of tables, and then click Next.
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.
Make sure that the Hide key column check
box is selected, and then click Next.
Click Finish on the last page of the Lookup Wizard.
Click Yes to save the table when you are prompted, and then name the table TestLookupWizard.
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.
On the View menu, click Datasheet View.
Note In Access 2007, open the table in the Datasheet View.
Click the drop-down arrow in the EmployeeLookup field.
Notice that two columns appear in the combo box.
Manually create a lookup field
Start Access.
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.
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.
Double-click Create table in Design View to create a new table.
Note In Access 2007, skip this step.
Type EmployeeLookup in the first row
under Field Name.
In the Data Type column, click Number.
In the Field Properties window, click the Lookup tab.
Iin the DisplayControl list, click Combo Box.
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.
Double-click the EmployeeID field, the LastName field, and the FirstName field to add the fields to the query
grid.
In the Close group on the Design tab, click Close to exit Query Builder.
Click Yes when you are prompted to save changes to the SQL
statement.
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.
Change the value of the ColumnCount property to 3.
Note This value represents the number of fields that are selected in the row
source.
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.
Type 2" in the ListWidth box.
On the View menu, click Datasheet View.
Note In Access 2007, open the table in the Datasheet View.
Click Yes to save the table when you are prompted, and then name the table TestLookupWizard2.
Click Yes to add a primary key to the table when you are
prompted.
Click the drop-down arrow in the EmployeeLookup field.
Back to the top
