Search Problems

[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

 

[Jeff Boyce]

Tonye

One approach would be to build a search form in which you set the criteria
you want to use. A command button on that form would then:

build an 'on-the-fly' SQL statement
run the SQL statement (that's all a query is anyway, underneath)

The trick will be to build a function behind the command button that tests
each potential search criteria control and either leaves out SQL (if empty)
or adds [possibly additional] WHERE phrases to the SQL string.

Consider building a query in design mode with a couple selection criteria,
then clicking the designer toolbar button (far left?) and selecting SQL
view. This will give you an example of how the SQL needs to look.

Good luck!

Jeff Boyce
<Access MVP>

 

[TH]

Thank you for your help.

Tonye

Tonye

One approach would be to build a search form in which you set the criteria
you want to use. A command button on that form would then:

build an 'on-the-fly' SQL statement
run the SQL statement (that's all a query is anyway, underneath)

The trick will be to build a function behind the command button that tests
each potential search criteria control and either leaves out SQL (if empty)
or adds [possibly additional] WHERE phrases to the SQL string.

Consider building a query in design mode with a couple selection criteria,
then clicking the designer toolbar button (far left?) and selecting SQL
view. This will give you an example of how the SQL needs to look.

Good luck!

Jeff Boyce
<Access MVP>