adding value list fields

[georgewash]

Hi,

I have a table which is used to create recipes. In the creation of the recipe I need to select each ingredient from the Products table. Each ingredient then needs the amount put in.

I can get the field to properly list the ingredients, but how do I set-up the table so I can add as many ingredients as possible? The number of fields for ingredients will be different with each recipe. Also, how do I link the amount field to the ingredient field when amount is an edit box and ingredients is a value list?

I need to do it this way because I will also be creating a Meal Planner table which will select recipes for each meal on a specific date. I will also have a Grocery List which will will aggregate all of the products and their amounts from the Meal Planner.

My thought was to create a script to each a new value list field for each new ingredient but I am unsure of how to get started.

Thanks.

[elo]

You don't make what tables you have clear, but I'll see if I can help.

You need at least three tables.

1) Recipes table

2) Ingredients table

AND

3) RecipesIngredients table

to support a many-to-many join.

Recipes will have the fields: recipe_id, RecipeName

Ingredients will have the fields: ingredient_id, IngredientName

RecipesIngrdients will have the fields: recipeingredient_id (optional field, but recommended), recipe_id, ingredient_id

The tables will need to be connected up by relationships in the data diagram with the proper _id fields connected.

Now, to add an ingredient to a recipe, you add a record to the RecipesIngredient table with the desired ingredient_id and the current recipe_id.