Pull data for multiple information

Gina, I actually do have Units of Measure which are text (Bunch, For Dusting, To Garnish and To Serve), they just were not stated on the screenshot I attached. I noticed that your examples, pinch etc. were surrounded by asterisks. Was that intentional, so they can be added onto other fields?. I was very impressed with the relationship road map the link took me to. I think I may have a better understanding of relationships because of it. Also I know when I do get to inputting all my recipes in my database, that recipes will relate to recipes, such as a crust of a pie. From the way the link was laid out I assume it is easy enough to add more tables as I need them. I noticed you have an 'Active' field in many tables, I was wondering why? Also curious why the Related Recipe table seems separate.

Please see below for more comments, for everyone.

Scott, I appreciate the breakdown of the table structure. You did loose me a little with the information around 'WHERE'. I found it quicker and more efficient to type in my criteria in the query column, maybe I am missing something here?. The 'robust recipe system' you mentioned, I guess is much like what you find in the link from Gina WOW!.

Please see below for more comments, for everyone.

Firstly you need to get the structure of the database right as described by Gina and Scott.  The tricky part is then designing a way to search on a variable number of ingredients.  You can then find an example of the methodology for doing this in MultiSelect.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

The model for this little demo file is:


In this think of Employees as analogous to Ingredients and Projects as analogous to Recipes.  ProjectEmployees is analogous to RecipeIngredients.

In my demo, the two buttons at the bottom of the main form open forms to (a) show projects to which any of one or more selected employees is assigned, analogous to finding recipes which use any of one or more selected ingredients, and (b) show projects to which all of one or more selected employees are assigned, analogous to finding recipes which use all of one or more selected ingredients.  The way it does is by using a multi-select list box to assign a value list to a hidden text box control which is then referenced as a parameter by the RecordSource query of a subform.  The query uses GetToken and InParam functions published by Microsoft.  These are in the basInParam module in my file, so you'd need to copy the module to your database.

Hmm, well the main problem is your tables are flawed.  Your way a recipe could never have more than 15 ingredients.  Ingredients should be by record not by field have a look at...

http://www.access-diva.com/dm15.html

Once your tables are adjusted you can then have a Main Form/Subform set-up and searching will be 200% better.  Oh, and for Units of Measure, you can include things like *dash*, *pinch* or *to tasted*, after all, it is your table.

Without knowing more about your table structures it's hard to give accurate advice, but from what you've shown onscreen I'd hazard a guess that your tables are not structured properly.

If you're trying to store Recipes, then you must first figure out what comprises a "Recipe". That would very likely be Ingredients, so at the very least you've need 3 tables:

tIngredients

=============

IngredientsID (autonumber, PK)

Ingredient

IngredientType

IndgredientDescription

etc etc

tRecipes

==================

RecipeID (autonumber, PK)

Recipe

RecipeDescription

RecipeCategory

PrepTime

CookTime

etc etc

tRecipe_Ingredients

=================

Recipe_IngredientID

RecipeID

IngredientID

Quantity

UnitOfMeasure

Notes

etc etc

tRecipe stores information about the Recipe ONLY, like "Chicken Cacciatore" or "Spanish Rice"

tIngredients stores information about the Ingredient ONLY, like "Kosher Salt" or "Fresh Pineapple"

tRecipe_Ingredients is the workhorse table that stores all the Ingredients for a specific Recipe. 

From there, you could have a Single Ingredient be used in Many Recipes, and a single Recipe could contain Many Ingredients.

With a data structure like this, you could easily determine where a specific Ingredient is used:

SELECT Recipe FROM tRecipes INNER JOIN tRecipe_Ingredients ON tRecipes.RecipeID=tRecipe_Ingredients.RecipeID WHERE tRecipe_Ingredients.IngredientID=1

If you want to search by an IngredientName:

SELECT Recipe FROM tRecipes INNER JOIN tRecipe_Ingredients ON tRecipes.RecipeID=tRecipe_Ingredients.RecipeID LEFT OUTER JOIN tIngredients ON tIngredients.IngredientID=tRecipe_Ingredients.IngregientID WHERE tIngredients.Ingredient='Kosher Salt'

Of course this is just the bare beginnings of a truly robust recipe system. You would certainly need other tables if you want to track Nutritional data, upsized recipes, etc.