1.                   

Open Lesson 2.4 from the Section 2 folder of your Exercise Files.

2.                   

Open the Vehicles table in Design view.

Click the Manufacturer ID field, and clear the Default Value field property.

Click the Year Field Name, then enter >1900 in the Validation Rule field property.

Click the Number of Cylinders Field Name, then enter >=0 in the Validation Rule field property.

3.                   

Click the Manufacturer ID Field Name and select the Lookup Wizard option from its Data Type pull-down menu.

Access warns you that this operation cannot be performed at this time because a relationship between the two tables was already established.

Click OK, and then close the Vehicles table and save the changes.

4.                   

Click the Relationships button () on the Database toolbar.

Right click the one-to-many relationship line that exists between the Manufacturers table and the Vehicles table, and then click Delete. Confirm the deletion.

5.                   

Reopen the Vehicles table in Design view and start the Lookup Wizard for Manufacturer ID.

At the first screen, make sure that the first radio button (I want the lookup column…) is selected and click Next.

At the next screen, select the Manufacturers table as the table that will provide the values for the lookup field, and then click Next.

At the next screen, add the Manufacturer ID and Manufacturer Name to the list of Selected fields by using the (>) button, and then click Next:

At the next screen, select Manufacturer Name in the #1 combo box, and then click Next.

6.                   

At the next screen, you should see the four vehicle manufacturers listed in alphabetical order. Make sure Hide key column is checked and then click Next:

The final page asks you to name the lookup field, leave the default name as Manufacturer ID, and click Finish.

Save the table when prompted. Close Design view for the Vehicles table and click the Relationships button again.

7.                   

You should see that Access has established a relationship between the two tables, but you can strengthen the relationship. Right click the line joining the two tables and click Edit Relationship:

When the Edit Relationships dialogue box appears, click Enforce Referential Integrity and then click OK. The relationship between the two tables is now classified as one-to-many, as denoted by the 1 and infinity signs at either end of the relationship line.

Close the Relationships window and save the changes.

8.                   

Open the Vehicles table in Design view one last time.

Select Lookup Wizard as Number of Cylinders’ Data Type to start the Lookup Wizard.

Click the second radio button at the first page marked I will type in the values I want and then click Next.

Under the heading Col1, type the following numbers, pressing Tab on your keyboard between each number: 0, 2, 3, 4, 6, 8, 10, 12, 16, 18. Click Next.

Leave the default name for this value list as Number of Cylinders and click Finish.

Close the Vehicles table Design view and then save the changes.

9.                   

Double-click the Vehicles table in the Database window to open the table in Datasheet view.

Enter the following data into the Vehicles table, making use of the lookup fields for the Manufacturer ID and the Cylinders fields:

10.               

Close the Vehicles table, and then close Microsoft Access 2003.