"branching" query, for want of a better term (1 Viewer)

shippeb

New member
Local time
Today, 17:50
Joined
Nov 17, 2020
Messages
2
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.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:50
Joined
May 21, 2018
Messages
6,268
NO. That is not proper database design. There are lots of ways to overcome what you are asking, but not that. You need to learn what a junction table is and how to do a one to many. There are lots of examples of recipe databases out there. I would google or search this forum.

This may be of interest.


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
Likely you simply need a field in the recipe table to tag a recipe by category. If you want to be able to give it many tags then you may need a child table. I think my category table would look like this.

TblCategory
CategoryID
CategoryGroup
CategoryType

Then It would have values like
1 Dessert Cookie
2 Dessert Cake
3 Dessert .....
4 Main Meat
5 Main Fish
.....
Appetizer

If I want to search for a recipe. I would have two cascading combos
Select dessert and the second comb shows only Cookies, Cakes, Pies.
I select Cake and it will then return all cake recipes.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:50
Joined
Feb 19, 2002
Messages
35,914
Here is a MS sample to get you started.
 

Attachments

  • RecipesTG_71.accdb
    676 KB · Views: 388

Users who are viewing this thread

Top Bottom