Step Number 1

Now we are going to set up the relationships between some of our tables. This will be useful, since it will allow us to treat a recipe and its ingredients as one logical unit. First, verify that the database window is visible and that you are on the tables tab. Then, right-click on tblRecipes and select Design View to open the table in design view.

Step Number 2

Edit the caption of the Recipe Type field to change it to Recipe Type. Now that we have the field caption set up correctly, we are ready to create a relationship between the tables.

Step Number 3

Now, change the data type of Recipe Type to Lookup Wizard.

Step Number 4

In the first dialog box that comes up, select I want the lookup column to look up the values in a table or query.

Step Number 5

On the next step of the wizard, select tblRecipeTypes as the table where you will be getting your values.

Step Number 6

On the next step, select the RecipeType field. This is the field that you want to look up your data from.

Step Number 7

This step of the wizard is a preview of the data in the column. This allows you to verify the data before the wizard completes.

Step Number 8

Now, label your lookup column as Recipe Type.

Step Number 9

Be sure and save the table when prompted to do so.

Step Number 10

Once you have saved, you should be returned to the table design window, as shown below.

Step Number 11

Wouldn't it be nice if the lookup column displayed its results in alphabetical order? Well, we can set that up easily enough by changing the row source of the lookup column. Change the Row source to

SELECT [tblRecipeTypes].[ID], [tblRecipeTypes].[RecipeType] FROM tblRecipeTypes ORDER BY [tblRecipeTypes].[RecipeType]; 

Step Number 12

Exit the table design view. When prompted about saving changes, do so.

Step Number 13

Now, right click on tblRecipes in the database window and select Open. Notice how the RecipeType field is now a drop down list box. This feature will really help out with data entry.

Step Number 14

Now, create the table tblRecipeIngredients as shown below. We are going to create a more complicated relationship between the recipes table and the ingredients table.

Step Number 15

Now, create the table tblIngredients, as shown below.

Step Number 16

Now, click on the Tools menu and select Relationships.

Step Number 17

The following window should appear next. In the relationships window, you can configure the way tables will interact with one another in terms of data. For now, only the Recipes and RecipeTypes tables are visible in the window, but we will soon add the RecipeIngredients and Ingredients tables.

Step Number 18

Right click on the relationships window and select Show Table from the popup menu.

Step Number 19

Select the table tblRecipeIngredients and then click Add.

Step Number 20

Go ahead and do the same thing for tblIngredients. Afterward, click on IngredientID in tblIngredients and drag it over IngredientID in tblRecipeIngredients. This will create a relationship between the two tables.

Step Number 21

The Edit Relationships dialog should now appear. Be sure to set it to Reinforce Referential Integrity, then click Create.

Step Number 22

Now, drag RecipeID from the table tblRecipe to RecipeID on the table tblRecipeIngredients. This will finish creating the many to many relationship between ingredients and recipes.

Step Number 23

Once again, reinforce referential integrity.

Step Number 24

Your relationships window should now look something like the following.

Step Number 25

Close the relationships window and save when prompted to do so.

Step Number 26

Step Number 27

[TABLERELATIONSHIPS STEP 27 DESCRIPTION GOES HERE]