Multiple Field Search

[TH]

Hi,

This problem is driving me crazy. I'm working on a database to store
recipes. One of the peices of information that is entered is Main
Ingredients. There are three different fields that you can do this in.
Once entered, the data goes to the Main Table and also to the Ingredient
table. It goes to the Ingredient table so that there can be one list of
ingredients over the three fields without duplicates. My problem is trying
to set it up to search for recipes by the Ingredient list from the
Ingredient table. I have set up a form/subform where you can pick which
ingredient you want to search by (this comes from a query based on the
Ingredient table). You can pick up to three ingredients. I have it set up
so that when you pick the first ingredient it searches the 3 fields in the
Main Table to see if the selected ingredient is listed in any of the three
fields. It then requeries the subform which is based on a query. The query
searches the 3 fields in the Main Table for the first ingredient chosen.
This works fine. What I can't figure out is how to be able to search by 2
or 3 ingredients. It needs to show recipes that have both ingredients or
all three ingredients depending on what is choosen. I also need to be able
to show the results if only One ingredient is chosen. This is is driving me
nuts. Any help would be greatly appreciated! Thanks.

TH

 

[Steve Schapel]

TH,

Before getting into this further, I have a question for you... Is the
design of your database set in concrete, or are you able to consider a
change? Part of your problem is the structure of your tables, and my
best advice would be to revise this first.

- Steve Schapel, Microsoft Access MVP

 

[Andrew Smith]

It's driving you crazy because you have some fundamental design problems
with your database. You should only ever need to store information once, and
tables should not need to store similar types of data in more than one
field. I's suggest you set up three tables as follows:

tblIngredients:
IngredientID (PK)
IngredientName
Anything else you want to store about the ingredient.

tblRecipes
RecipeID (PK)
RecipeName
Other data specific to the recipe - this does NOT include the list of
ingredients. It may include cooking instructions etc.

tblRecipeIngredients
RecipeID (foreign key linked to tblRecipes)
IngredientID (foreign key linked to tblIngredients)
IngredientAmount
You may also want unit of measure here, but this could be in
tblIngredients if it's always going to be the same for each ingredient.

If you want to categorise your recipes the you could also have a recipe
category table and add the category ID to tblRecipes. Similarly if you want
to categorise the ingredients.

The form for entering your recipes should have a subform based on
tblRecipeIngredients for entering the ingredient list. This could have a
combo box for selecting the ingredient (so you see a list of ingredient
names, but actually store the ingredientID).

For the search you simply need to search on tblRecipeIngredients (or, more
likely, a query built from all three tables).

 

[TH]

Andrew,

Thanks for responding. I want to be able to have up to three different main
ingredients. This should be seperate from the list of complete ingredients.
This is because I want to be able to sort recipes and show all that have
just one key ingredient (ie just chicken), recipes that have two key
ingredients (ie. chicken *and* cheese), or recipes that have three key
ingredients (ie. strawberries *and* bannanas *and* mellon).

What you wrote probably will work for what I want but for some reason I just
can't figure it out. I not used to using multiple tables. One more
question - the id fields should be numbers right?

Thank you for your time and patience. It is much appreciated.

Tonye

 

[TH]

Steve,

I got to thinking and I was wondering if there was anything I could do other
than redesign my database. It would be possible but I've put a lot of work
in it. Any other options other than redesign would be wonderful. Thanks.

Tonye

 

[Andrew Smith]

To answer the last question first - the ID fields could be numbers or text.
They must be unique though, and you need a system for ensuring they are
unique. If you use autonumbers then you get a guaranteed unique number that
you don't have to generate yourself, so that would be my choice. You never
need to see the IDs or know what they are - unless you want to.

I'd probably keep main ingredients in the same table as the full list of
ingredients as I imagine you'd need to store the same types of information
for both the main and the other ingredients. You could add a yes/no field to
the table and set it to yes for the main ingredients. You would not be
limited to 3 main ingredients by the database design - if you need to ensure
that you have at least 1 and no more than 3 main ingredients per recipe then
you need some other way of doing this with this design.

With the 3 table design it will be very easy to find recipes that contain
any individual main ingredient (eg "strawberries"). It will be a bit harder,
but not too hard, to show the number of main ingredients in each recipe. I'm
not sure how to go about grouping and sorting by combinations of main
ingredients as you need to do. I'm pretty confident it could be done, but it
would take me some time to work out how!

 

[Steve Schapel]

Tonye,

I have read Andrew's post, and I agree with him 100% in his
suggestions.

I appreciate you have put a lot of work in getting your database to
its present stage, and the thought of re-working it is hard to
contemplate. Believe me, anyone who has become proficient in
relational databases will know what that feels like! Yes, there are
ways to get the desired results using your existing structure, but it
would involve some complex queries, probably writing a user-defined
function, and I would imagine this would be at least as difficult as
fixing your tables. Plus, this will be a great opportunity to learn
correct design principles.

You have fallen into what is sometimes known as the "fields as data
trap". Yes, each recipe has more than one ingredient, and you want to
record up to 3 key ingredients per recipe. Putting in 3 columns for
this is the kind of thing that might be done in a spreadsheet or stuff
like that, but this idea really has no place in a database. Database
thinking is to recognise a one-to-many relationship, and therefore the
need to use 2 related tables to reflect this reality. In other words,
the table structure is dictated by the real-life nature of the data.
So your database needs a table where you can record which ingredients
go in which recipe, and this *must* be a separate table, due to the
fact that there can be more than one ingredient for any given recipe.
This is the table which Andrew called RecipeIngredients. In this
table, the recipe is represented by a unique identifier, probably a
number, what Andrew named RecipeID, which references the Primary Key
field in the Recipes table. And the ingredient is represented by a
unique identifier, probably a number, what Andrew named IngredientID,
which references the Primary Key field in your Ingredients table,
which is a master list of all ingredients which you might want to
track. This is actually elementary, and a lot easier than it might
appear to you at this stage. And when you have your data arranged in
this configuration, doing your searches, counts, summaries,
statistics, data manipulation, etc, etc, becomes very easy... which is
the whole point of using a programme like Access in the first place.

Hope these comments are helpful and encouraging

- Steve Schapel, Microsoft Access MVP

 

[TH]

Andrew,

Thanks for the help. I will work on it and see where I get.

Tonye

 

[TH]

Steve,

Thanks for the help. I will work on it and see where I get.

Tonye