Form with all possible related ingredients as a subform

[jzollo]

Hello again, this is my second post

I am a chef and am creating a database with two main tables: recipes and ingredients. I want to have a recipe related to many ingredients.

What I would like is a form based on the recipe table with the ingredients all listed below with a y/n check box. Then when checking the box that ingredient would be related to the focused recipe.

However, and I believe I would need to have a many to many relationship, one recipe will have many ingredients but those same ingredients will appear in many recipes.

In the end I want to create reports of selected recipes with its ingredients listed below them, and a grocery list of all ingredients needed for a week's worth of recipes.

Thank you

JZ

 

[MarkK]

Hello there, and welcome to the forum. It's not clear to me from your post what information you are looking for.

One observation I have is that a Many-To-Many relationship requires at least three tables, so you might have a Recipe table, Ingredient table, but where the rubber hits the road will be your RecipeIngredient table, which is where you'll specify the quantity of an ingredient that belongs to a recipe, and maybe any special prep required for that ingredient, for that recipe.

Not sure if this helps your thinking too, but can a recipe ever function as an ingredient? Like, maybe you have a white sauce recipe, and some other recipe requires a quantity of white sauce as an ingredient. How should that be handled, or perhaps that's beyond the scope of your current system.

Hope this helps,

 

[burrina]

To further add to Mark's suggestions. Here are the tables I would use and do use in one of my db's.

tblFoodCategories
tblIngredients
tblRecipeIngredients
tblRecipes

HTH