Pull data for multiple information

Question

Hi, I would like some help. I am using Access 2007 and as I have just started to create some databases for home use.  The above is a screenshot of a Form I have created, in the background (as tabs) I show the 2 Tables I need help with.

I am trying to have it so I can run a query on one or more ingredients to return all recipes with this information.

At the moment I have 1 column of ingredients in the "Ingredient Listing" table and the same for the units of measure in the "Unit of Measure Listing" table. I think this should stay this way.

I have some basic queries set up, to pull information from my "Recipes" table which holds all the information. I have 15 separate columns to hold the unit of measure information and another 15 to hold the ingredient information. I am willing to have 15 identical/duplicate columns in the tables holding this information (instead of one), but there must be a better way.

Also I have tried to use information I have found to solve this problem, but I am unable to get what I think is the best and easiest solution to work. I found this "set Fieldb = fielda" and have tried to put it in an Expression Builder (with my database details) in the tables to duplicate information from 1 column to 15, no success.

If there is a way to query an ingredient without inputting its name under each (15) column, in separate rows I would be very interested. With the number of ingredients there are in a recipe, there is not enough rows in a query for me to do this.

I have attached this particular screenshot so you can see that some recipes show an ingredient more than once (generally with different units of measure). Also not all ingredients such as salt and pepper have a unit of measure to be recorded.

I feel like it is pointless to continue entering hundreds or thousands of recipes into this database until I can search for want I want properly. Any help with this would be greatly appreciated.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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.