Database: From recipe to shopping list

[schwabe]

Hello everyone,

I am working on a database which I want to use to generate a shopping list for a given menu.

So, I have a created a tabMenu with the fields
ID refCourse1 refCourse2, refCourse3, refCourse4

Each Course field references to a table tabRecipes with the fields
ID numRecipesName

I also have a table tabGroceries with the fields
ID txtGroceryName refSource numPricePerUnit

The refSource field references to another table providing the information where to buy the stuff.

The report at the end should be something like:

Menu: Italian Dinner
Course1: Antipasti
Course2: Pasta
Course3: Secondi
Course4: Dolce

Groceries Amount Price Per Unit Price
Fennel 10 1.49$ 14.90$
Arugula 10 2.99$ 20.90$
...

I am wondering how I can assign to each recipe in my database a list of ingredients?

Thanks for your help in advance,
Benjamin

[June7]

tabMenu is not a strictly normalized structure but might be workable for your requirements, as long as you don't want meals with more than 4 courses. With this structure, if you want a report that lists the courses vertically, will require a UNION query. Otherwise, restructure the table.

For the grocery list, you need another table that associates recipe with grocery items.

tabRecipeItems
RecipeID
GroceryID
Quantity

[schwabe]

hi June7,

thank you for the answer - exactly what I was looking for.

Benjamin

[June7]

You might find this of interest http://office.microsoft.com/en-us/te...001018635.aspx