Query within a query

[TimTDP]

In Access 2000, I have a table called "tblRecipe"
I also have the following tables that are linked to tblRecipe:
tblSupplierIngredient and tblOwnRecipe

A recipe can be made by simply only having SupplierIngredients. However a recipe can also be made by having SupplierIngredients and a recipe in tblRecipe (stored in tblOwnRecipe)

My problem is calculating the cost of a recipe. If the recipe only consists of SupplierIngredients the calculation is easy. However I am struggling to calculate the cost of a recipe if it contains a combination of SupplierIngredients and an OwnRecipe because the OwnRecipe may include an OwnRecipe so I don't know the cost of the new recipe

any ideas on how to solve this?

 

[Golom]

How many "OwnRecipie"s could appear in a recipe?

If there's never more than one then you can pull it off but if it's an arbitrary number then you need to either write some code to do this or look at some more advanced techniques like nested sets to structure your tables.

At a minimum, we would need to see a bit more of your table layouts to see how this might be done.