If you recall our discussion about relationships in the last exercise, you will remember that the Order Details table contained two primary keys, OrderID and ProductID. However, when viewing the Order Details table in Datasheet view, the ProductID value was not shown, but rather the name of the product that corresponded to the ProductID was shown. This makes data entry into a table much easier if you know the name of the product rather than having to memorize any ProductIDs:

 

 

The product names are instead visible because a lookup field was created instead of using the ProductID. To see how one is created, let’s remake the same lookup field used in the Northwind sample database.

Imagine that Northwind Traders is just starting up and the relationship between the Order Details table and the Products table does not exist yet. Open the Order Details table in Design view and select Lookup Wizard in the Data Type field for ProductID:

 

 

The Wizard will launch:

 

 

Since the values for the lookup table are based on another table, the first radio button option is fine. Click Next:

 

 

The Wizard gives you the option to extract data from tables and/or queries. The data needed for this lookup field is located in the Products table, so highlight it and click Next:

 

 

The next page of the Wizard is similar in design to the design of a query by the Query Wizard. Click ProductID from the list of available fields and click the (>) button to transfer the field. Do the same for the ProductName field.

Note that the wording of this page of the Wizard is a little tricky; it asks which fields we want to include in the lookup column. We don’t actually want to show the ProductID, we want to show the ProductName. However in order to properly complete the lookup field query, we need to include the ProductID because without it, making the query show the corresponding ProductName is impossible. If you are designing your own lookup fields and find that you simply aren’t getting the right data or any data at all, your query probably isn’t specific enough.

 

 

The next page of the Wizard allows you to sort the items in the lookup field by sorting a certain field in ascending or descending order. For the purposes of names, sorting ProductName in Ascending order is a good choice. Select ProductName from the combo box and click Next:

 

 

Sure enough, we have the lookup field results the same as initially established in the sample database. Clicking the Hide key column checkbox to uncheck it will display the ProductID alongside the ProductName field. Click Next to continue to the last step of the wizard. Here you can give the lookup field a name, or leave the default name (ProductID) as is. Click Finish to exit the Wizard.

If you recall, this example was built before a relationship existed between the Products Table and the Order Details table. Before you can start using the lookup field in the Order Details table, Access must first save the changes that were just implemented in the Order Details table. As a final step, open the Relationships window () and click and drag the ProductID field in the Products table to the ProductID field in the Order Details table. Remember to Enforce Referential Integrity. This establishes the relationship between the two tables.