Hi everyone, I hope someone(s) can help me, if only by telling me the right words to use for this!
Let's suppose I am building a database of recipes, with ingredients. Because the list of ingredients for savory dishes (soup, stew, etc) could be so long and is so different from the list of ingredients for cookies or cakes, it seems like I would want to have one table for savory, one for cookies, etc. My thinking at the moment is a table for cookies (for example) with a Yes/No column for each ingredient, and as I enter a new recipe I would check the appropriate boxes.
Now suppose I have too many eggs in my kitchen, and I want to use them all up. I could just have a query that connects to all of those tables and returns all recipes that use eggs. But that would return egg drop soup, Yorkshire puddings, angel food cake, chocolate chip cookies, and everything else that uses eggs. Which is fine if you just want to use up eggs, but not so helpful if you want something sweet!
But what I want is a query that first asks "recipe type?" with a drop-down list, then "ingredient?", and returns only those recipes from the appropriate recipe type that use the chosen ingredient.
I can see this being a first table with all recipes in it, with the type chosen from a specific list (yorkshire pudding=side, meringue=dessert), linked to a table with ingredients for each recipe, as yes/no columns (egg, flour, water, sugar, etc). I have built that, to the point where it returns all recipes of the chosen type and provides all of their ingredients. So if I choose "dessert", it returns all desserts no matter whether they use eggs or not. The Yes/No boxes help in sorting out which recipes use eggs, but I want to reduce the work on the user.
So - can I write a query that "picks" which table to look at depending on the input, and then selects which column within that table to look at, and returns only those items with "yes" in that column?
Many thanks for any help!
E.
Let's suppose I am building a database of recipes, with ingredients. Because the list of ingredients for savory dishes (soup, stew, etc) could be so long and is so different from the list of ingredients for cookies or cakes, it seems like I would want to have one table for savory, one for cookies, etc. My thinking at the moment is a table for cookies (for example) with a Yes/No column for each ingredient, and as I enter a new recipe I would check the appropriate boxes.
Now suppose I have too many eggs in my kitchen, and I want to use them all up. I could just have a query that connects to all of those tables and returns all recipes that use eggs. But that would return egg drop soup, Yorkshire puddings, angel food cake, chocolate chip cookies, and everything else that uses eggs. Which is fine if you just want to use up eggs, but not so helpful if you want something sweet!
But what I want is a query that first asks "recipe type?" with a drop-down list, then "ingredient?", and returns only those recipes from the appropriate recipe type that use the chosen ingredient.
I can see this being a first table with all recipes in it, with the type chosen from a specific list (yorkshire pudding=side, meringue=dessert), linked to a table with ingredients for each recipe, as yes/no columns (egg, flour, water, sugar, etc). I have built that, to the point where it returns all recipes of the chosen type and provides all of their ingredients. So if I choose "dessert", it returns all desserts no matter whether they use eggs or not. The Yes/No boxes help in sorting out which recipes use eggs, but I want to reduce the work on the user.
So - can I write a query that "picks" which table to look at depending on the input, and then selects which column within that table to look at, and returns only those items with "yes" in that column?
Many thanks for any help!
E.
