items>recipes>dish

[chefdaveross]

I am trying to calculate a query that lets me populate totals for dishes on my menu.

I have an Item query (mayo, butter, rolls, lettuce, lobster meat, fries), with pricing
and
I have a recipe query (lobster salad, potato salad) (items grouped and calculated)

I would like to create a query so I can select both Item and Recipe data into a Dish Form, that can calculate totals for the dish.

I have only been able to create forms based off one query but not able to combine them.

Hence for a dish form, I would have Lobster Roll
and select, lobster salad from recipes and also select lettuce from items

 

[jzwp22]

What does your table structure (table names with fields and relationships between tables) look like?

I'm guessing you have something like this

tblItems
-pkItemID primary key, autonumber
-txtItemDesc
-unitsize
-fkUnitOfMeasureID foreign key to tblUnitsOfMeasure
-unitprice

tblRecipes
-pkRecipeID primary key, autonumber
-txtRecipeName
-numberofservings or amount yielded from recipe
-fkUnitOfMeasureID foreign key to tblUnitsOfMeasure


tblRecipeItems
-pkRecipeItemsID primary key, autonumber
-fkRecipeID foreign key to tblRecipes
-fkItemID foreign key to tblItems
-amountneeded
-fkUnitOfMeasureID foreign key to tblUnitsOfMeasure

tblDish
-pkDishID primary key, autonumber
-txtDishName

tblDishConstituents
-pkDishConID primary key, autonumber
-fkDishID foreign key to tblDish
-fkRecipeID foreign key to tblRecipes
-amountused
-fkUnitOfMeasureID foreign key to tblUnitsOfMeasure

tblUnitsOfMeasure
-pkUnitOfMeasureID primary key, autonumber
-txtUnitOfMeasure

 

[chefdaveross]

Yes almost the same,
but notice how you can only select recipes from the dish table and not items,
i would like to be able to have the option to have both.
thanks

 

[jzwp22]

I see what you are saying. The food created by the recipe is also an item. This would be similar to parts that are made up of other parts. That requires a junction table

tblItems
-pkItemID primary key, autonumber
-txtItemName
-unitsize
-fkUnitOfMeasureID foreign key to tblUnitsOfMeasure
-unitprice

tblItemConstituents
-pkItemConstID primary key, autonumber
-fkItemID foreign key to tblItems
-fkItemConstituentID foreign key to tblItems (also)
-amountofconstituent
-fkUnitsOfMeasure

Only items that are composed of other constituents will have records in tblItemConstituents.

tblDish
-pkDishID primary key, autonumber
-txtDishName

tblDishConstituents
-pkDishConID primary key, autonumber
-fkDishID foreign key to tblDish
-fkItemID foreign key to tblItems
-amountused
-fkUnitOfMeasureID foreign key to tblUnitsOfMeasure

tblUnitsOfMeasure
-pkUnitOfMeasureID primary key, autonumber
-txtUnitOfMeasure