how to link multiple fields to one

[DavidT]

Hi,

This is my first post and I'm pulling my hair out on this one!

I have a db with 2 tables. one is a recipes table with fields for ingredient 1...ingredient cost, ingredient 2...ingredient cost, etc etc. There are fields for 10 ingredients.

The 2nd table details all the ingredients and their costs and other attributes.

I want the db, when a user types in the ingredient in 'ingredient 1', to look up the cost for that ingredient and display it in ingredient 1 cost. Then do the same thing for each ingredient.

Maybe I'm going completely the wrong way about the structure, but I've tried making a relationship between the 2 tables with each ingredient in the recipes table to the single ingredient id field in the ingredients table.

It works when I just have ingredient1 linked, but when I link the other ingredients, it all falls over.

I'm trying to teach myself how to do it, but I'm lost now!

Any help appreciated

[bcooney]

Since you have a many-to-many relationship btw Ingredients and Recipes (each ingredient can be in many recipes; each recipe can have many ingredients), you need a third table. This new table is referred to as a join table, let's call it IngRecipe, and it will store the two foreign key IDs (IngredientID and RecipeID).

Search for join table.