TH wrote:
I am (still :) working on a recipe database. Now I am trying to figure out
how to set it up for an ingredient search. What I want it to be able to do
is to search by one ingredient, sometimes by two, and sometimes by three.
There won't always be a second and third ingredient to search on. It will
depend on how the user wants to search.
The database set up with multiple tables mainly one for all the details
related to the recipe and one for the ingredients specific to the recipe.
Each ingredient is in a seperate record. Each ingredient record is tied to
the Recipe table by a Recipe ID number. These two tables have a one to many
relationship and the join type is to include records where the joined fields
from both tables are equal.
I was thinking of using queries but I can't figure out the logistics of it
so it will perform the three things above. I created a crosstab query where
the columns are names of the ingredients. The query tabulates how many of
each ingredient is in a particular recipe. I don't know how to do a search
here based on which ingredient/s is/are selected. I don't know if you can
tell a query which field to place the search criteria on based on user
selection. I don't know where to go from here or if this is even the right
first step.
I know nothing about SQL but am willing to learn.
Thank you for your time to anyone who is able to answer. I just have no
idea how to do this. I am totally clueless here. Please help if you can.
Thanks again.
Tonye
Here is an approach you can consider. This is as assumption of what you have.
Change names, etc to suit your situation.
I'll assume you have a recipe table, a reciple detail table, and an ingredients
table. A recipe can have 1 to many details. Each detail will have 1
ingredient.
Recipe1 Meatloaf
Detail1 1Pound Ingredient Hamburger
Detail2 1/2 tsp Salt
Detail3 1/2 tsp Pepper
Recipe2 Rice dish
Detail1 1 cup Rice
So, we have a RecipeID (Primary key, autonumber) in the recipe table. We have a
RecipeID (foreign key, long int) in the Detail table thus linking the two
tables. We also have an Ingredient key IngID (foreign key, longint) in the
Detail table that links to the Ingredient table. The ingredient table has and
Ingredient key IngID (primary/autonumber) and a description.
I'll call the 3 tables Recipe, Detail, and Ing. The main recipe form is called
Recipe.
Create a new query (Query/New/Design). Select the Ing table. Select the IngID.
and description. Sort on the description. Save it and namel it IngQuery.
What we want to do is return all records in the recipe that has an ingredient
you are filtering/searching on. OK, on the form Recipe add 3 combo boxes; call
them Filt1, Filt2, and Filt3.
Make IngQuery the RowSource for Filt1, Filt2, and Filt3 in the form.
Add a command button called CommandSearch. Add one more called CommandClear
In commandClear's OnClick event enter
Me.Filt1 = Null
Me.Filt2 = Null
Me.Filt3 = Null
Me.Requery
In CommandSearch's OnClick event enter
Me.Requery
Save the form's design
Create another new query (Query/New/Design). Select the table Detail. Drag the
field RecipeID and IngID (ingredient id) to the columns. In the criteria for
IngID enter
Forms!Recipe!Filt1
Forms!Recipe!Filt2
Forms!Recipe!Filt3
The second and third lines will be in the OR rows
In the third column enter
IsNull(Forms!Recipe!Filt1) And IsNull(Forms!Recipe!Filt2) And
IsNull(Forms!Recipe!Filt3)
In the 4th criteria row, enter True
From the query menu, click the inverted M (totals). This will convert the query
to a Totals query. Make sure in the Total row, RecipeID is set to GroupBy and
that the other two columns are set to WHERE.
Now save this as query as RecipeFilter. The reason we are using a Totals query
is that we want only 1 record to be returned. If we didn't have a totals query,
if a recipe had all 3 ingredients, you'd get 3 records returned for the same
recipe.
Now open your form Recipe in design mode, pop up the Form's properties, select
the Data tab, click onto the Recordsource row, and press the triple dot. This
will put you into the query builder for the form. If this is not already a SQL
statement, drag the asterisk down for the Recipe table (or your query). Then
look on the menu bar for a yellow plus sign (ShowTable) and press that. Select
the Query tab and select RecipeFilter. Make sure there is a link (line) between
the RecipeID for each table.
OK. Save the Recordsource, save the form and try it out,