By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,139 Members | 1,207 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,139 IT Pros & Developers. It's quick & easy.

Search Problems

P: n/a
TH
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


Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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,
Nov 12 '05 #2

P: n/a
TH
Thank you so much for your help and the time you have taken to give it. I
tried what you suggested. It works if you are looking for one "or" two "or"
three ingredients (which might be handy). However, I realized that I failed
to make clear in my post that I need to be able to look for one and two and
three ingredients (ie. apples "and" strawberries "and" pears). A person may
just enter one ingredient (apples), may enter two (apples "and"
strawberries), or may enter three (ie. apples "and" strawberries "and"
pears).

I am sorry that I failed to make this clear the first time. However, like I
said, the or will come in handy too. I just don't know how to do the and.
Would you happen to know how?

Thanks
Tonye
"U N Me" <un**@together.com> wrote in message
news:3F**************@together.com...
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,

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.