Build related tables with the Access 2007 Lookup Wizard

The Access 2007 Lookup Wizard makes it easy to create a related table. This walk-through demonstrates how it works.

You've just created an Employee Records table for your Employees database that lists personal information (such as Address, Phone Number, and Birth date) for each employee. Now you need to create a second table that lists each employee's work location and job title. This new table will use the Employee ID field in the Employee Records table as its foreign key field, a field that refers to the primary key field in another table. Follow these steps to create the related table:

  1. Click the Create tab and then click the Table Design button in the Tables group.
  2. Enter the Location and Job Title fields as text fields with a Field Size of 20.
  3. Switch to Datasheet View.
  4. Click Yes.
  5. Enter Job Assignments as the table name and click OK.
  6. Click No to create a primary key.
  7. Display Tables in the Navigation pane.
  8. Click the Datasheet tab under Table tools and click the Add Existing Tools button from the Fields & Columns group.

Figure A

  1. Click + to display the field names for the Employees table in the Field List.
  2. Click and drag the Employee ID field to the left of the Location field in Datasheet View.

Figure B

Figure C

  1. Click Next
  2. Click Next twice moreand then click Finish.

To add a record, the user selects the Employee ID number from the list and then enters his or her location and job title.

Figure D

Miss an Access tip?

Check out the Microsoft Access archive and catch up on other Access tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Editor's Picks