Table Design  «Prev 

Using lookup fields in Access

Creating a lookup field

  1. The Consultants table has been opened in Consulting307.mdb in Design mode. You are going to add a new field called ConsultingTypeID. In the blank cell under the SSN field, type ConsultingTypeID in the Field Name field, then press Tab. Just click the cell, and we will fill it in for you.
  2. Now we want to show the drop-down list for the Data Type property. Click the arrow in the cell next to ConsultingTypeID.
  3. Now is time to start the Lookup Wizard. Pick Lookup Wizard from the bottom of the Data Type property drop-down list.
  4. This is the first screen of the Lookup Wizard. Here you have the choice of having Access look up the values in another table or entering your own values. For our purposes, we are going to look up the items in another table, which is the default. Click Next >.
  5. This page lets you specify the record source you want to use for the lookup. Click the ConsultingTypes table in the list of tables.
  6. You have now picked the record source. Click Next > to move to the next screen.
  7. Now you get to pick which fields you want to include in the list to be displayed. For the purposes of this simulation, you will pick all the fields. Click the >> button to select and move all the fields. In this case, the only field available is ConsultingTypeID and its description.
  8. Now all the fields are chosen. Click Next >.
  9. This page shows you how the fields will be displayed in the lookup list. You may notice that the ConsultingTypeID field is hidden by default. Click Next >.
  10. Okay, almost there. You get to specify which caption you want to be displayed for the lookup column. You are going to replace the ConsultingTypeID entry in the field labeled "What label would you like for your lookup column?". You would press Delete because your cursor is already in the lookup field.
  11. Type Consultant Type in the "What label would you like for your lookup column?" field. The Lookup column has now been created. Click Finish.
  12. This is what the Lookup properties will be set to once you click Finish.