Multiple Search Criteria

[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 three things:
1. Search based on just one ingredient
2 Search based on two ingredients so that the recipe would have to have
both ingredients to show up in the results
3. Search based on three ingredients so that the recipe would have to
have all three ingredients to show up in the results.
Doing just one ingredient is easy. It's when I get to two and three that I
have a problem. The thing is there won't always be a second and third
ingredient to search on. It will depend on how the user wants to search.

I have the database set up with multiple tables. A couple of these include
one for all the details related to the recipe and one for the ingredients
specific to the recipe. 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 can get it
to where it shows recipes that have say apples or strawberries. When I put
these two on the same line to create an And criteria I get no results even
though there are recipes fitting those criteria. Is it looking for both of
these to be in the same record, same field? How do I get it to look through
all of the ingredients for a particular recipe so it does what #2 and #3
above indicate?

Thank you for your time to anyone who is able to answer. I just have no
idea how to do this.

Tonye

 

[HSalim]

TH,
It would help to have table details - saves long explanations too

instead of using Apples And Strawberries, try using Apples OR Strawberries

HS

 

[Tonye]

HS,

Thank you for answering. What do you mean by table
details? Also, you suggested using Apples OR
Strawberries. However, it needs to be Apples And
Strawberries. Part of the purpose of the database is to
be able to find recipes that contain a combination of
ingredients if need be. Any ideas?

Tonye

 

[HSalim]

What are the columns(fields) and datatypes of your table?
And how are you currently getting your resultset to return Apples and
Strawberries?

I think I know what a thing or two databases , but ...

HS

 

[TH]

There are two main tables. The first is Recipes; it holds information about
the recipe that is required only once for each recipe - Instructions,
nutritional information, who the recipe came from, etc. The second table is
just for ingredients (text field) and their amounts since there will be more
than one ingredient for each recipe. The fields are joined by a common
field - Recipe ID (a number). The Recipe ID fields in the two tables are in
a one to many relationship.

Currently, I am unable to get the resultset to return Apples and
Strawberries. I can only get it to sort on one ingredient. I think the
reason for this is that there are more that one record in the Ingredient
table for each recipe in the Recipe table. I don't know how to get it to
look at the record for each recipe as a group and to be able to see if that
group has two or three ingredients.

Thanks for helping.

Tonye

 

[HSalim]

OK lets try this again.
The answer I'd like to see is:
Recipes (Table)
----------------------
ID - Number - AutoNumber
Description - text 50
....
Sample Data
1. Peach Cobbler, ...
2. Apple Strawberry Disaster, ...
3. Experiments with beer, curry powder and Chocolate (Bad!)

Instructions
----------------
RecipeID - foreign Key (Recipes.ID)
StepNumber - Number
Description - TExt 255

Sample Data
1, 1, blend peaches
1,2, Do something else
1,3, Do something else
3,1, My Kids idea of good food
3,2, Throw everything in
....

Ingredients
RecipeID
IngredientName
Quantity
Measure

Sample DAta
1,Peaches, 2, each
2,Apples, 3, Each
2,Strawberries 10, oz
3, Beer, 32, oz
3, Curry Powder, 1 tsp


Remember, we are NOT at standing by your side and looking over your
shoulder.
Give us the means to help you.

HS

 

[TH]

Recipe Ingredients Table
---------------------------------
Recipe ID - Number - Long Interger
Ingredient ID - Text - Field Size 50
Main Ingredient - Yes/No
Ingredient Amount - Number - Long Interger
Unit Of Measure - Text - Field Size 50

Sample Data
------------------
1
Apples
Yes
6
Apples

Recipe Table
----------------------
Recipe ID - Number - Long Interger
Print This - Yes/No
Recipe Title - Text - Field Size 50
Instructions - Memo

1
Yes
Apple Cobler
Combine all ingredients


Hope this give you a better idea. Sorry for being too vague.

Tonye

 

[HSalim]

ok TH,
As I suspected - each of your ingredients are listed in separate rows.

Here's a suggestion which I dearly hope you will adopt. Please avoid using
spaces in your
table and column names. Use Mixed case to distinguish CompoundNames

Back to our regularly scheduled programming.
Create a query - call it qryPivotIngredients
TRANSFORM Count(Ingredients.IngredientAmount) AS Quantity
SELECT Ingredients.RecipeId
FROM Ingredients
GROUP BY Ingredients.RecipeId
PIVOT Ingredients.IngredientID;

You can now use syntax similar to what you proposed

Select * from [qryPivotIngredients] Where [Apples]>0 AND [Strawberries]>0

There are a few problems with this approach.
1. You cannot use wild card searches because each ingredient is a column
2. You will have to use some means to ensure that the column names are
entered
exactly as theyare in the database - this is best achieved using
list/comboboxes.
3. There is a limit to the number of columns in a cross tab query -
It is either 256 or 1024 - not a lot for this table which could easily
exceed those numbers
.. Further performace is inversely proportional to the number of ingredients
and rows in the table.
4. Cross Tab queries use proprietary extensions to SQL that are not easily
portable to other databases including SQL server.
If you are using Access as a client to other databases, your query will
run but
performance will be even more dismal
You probably dont care about this, but for the sake of those cowboy who
might see
this posting and shout "YeeeeeeHAW!", let this be a fair warning.

So what's a cowboy to do?

Use a temp table and popluate it using VBA using two passes - all you need
is a table
with one column - recipeID, so lets call it Results

(Well, first delete the temp table, incase it has any rows from a previous
operation)
Delete * from Results

'1. Get list of recipes with first item
insert into Results(recipeID)
select distinct RecipeID from RecipeIngredients where INgredientID like
"straw*"

' Delete all Recipes that do not match on other ingredients, because this is
an AND search
2. Delete REsults.* from Results
left join RecipeIngredients on results.recipeID =
RecipeIngredients.REcipeID
where RecipeIngredients.IngredientID = "apples" or
RecipeIngredients.IngredientID Like "Peac*"
and RecipeIngredients.IngredientID is null


I hope you know how to do this via VBA. Post back if you need help there.

HS

 

[TH]

Thank you so much for all the help you are giving me. I figured out how to
do the first part (creating the query) but I'm having trouble with the
select statement. I set up the query using the query design grid since I'm
more comfortable with it that with SQL. Here is what I set up.

RecipeID Ingredient ID The Value:
IngredientAmount
RecipeIngredients RecipeIngredients RecipeIngredients
Group By Group By Count
Row Heading Column Heading Value

I tried putting "Apples" under IngredientID and got the correct results.
Then I tried putting "Apples" and "Strawberries" under IngredientID and did
not get any results. There is one record that matches this criteria though.

The second part of your e-mail contained directions for creating a temp
table. Is this something I would need to do since since this database will
be run exclusively in Access? From what you said I didn't think so but I
just wanted to check.

Again, thanks for the time you are taking to help.

TH

ok TH,
As I suspected - each of your ingredients are listed in separate rows.

Here's a suggestion which I dearly hope you will adopt. Please avoid using
spaces in your
table and column names. Use Mixed case to distinguish CompoundNames

Back to our regularly scheduled programming.
Create a query - call it qryPivotIngredients
TRANSFORM Count(Ingredients.IngredientAmount) AS Quantity
SELECT Ingredients.RecipeId
FROM Ingredients
GROUP BY Ingredients.RecipeId
PIVOT Ingredients.IngredientID;

You can now use syntax similar to what you proposed

Select * from [qryPivotIngredients] Where [Apples]>0 AND [Strawberries]>0

There are a few problems with this approach.
1. You cannot use wild card searches because each ingredient is a column
2. You will have to use some means to ensure that the column names are
entered
exactly as theyare in the database - this is best achieved using
list/comboboxes.
3. There is a limit to the number of columns in a cross tab query -
It is either 256 or 1024 - not a lot for this table which could easily
exceed those numbers
. Further performace is inversely proportional to the number of ingredients
and rows in the table.
4. Cross Tab queries use proprietary extensions to SQL that are not easily
portable to other databases including SQL server.
If you are using Access as a client to other databases, your query will
run but
performance will be even more dismal
You probably dont care about this, but for the sake of those cowboy who
might see
this posting and shout "YeeeeeeHAW!", let this be a fair warning.

So what's a cowboy to do?

Use a temp table and popluate it using VBA using two passes - all you need
is a table
with one column - recipeID, so lets call it Results

(Well, first delete the temp table, incase it has any rows from a previous
operation)
Delete * from Results

'1. Get list of recipes with first item
insert into Results(recipeID)
select distinct RecipeID from RecipeIngredients where INgredientID like
"straw*"

' Delete all Recipes that do not match on other ingredients, because this is
an AND search
2. Delete REsults.* from Results
left join RecipeIngredients on results.recipeID =
RecipeIngredients.REcipeID
where RecipeIngredients.IngredientID = "apples" or
RecipeIngredients.IngredientID Like "Peac*"
and RecipeIngredients.IngredientID is null


I hope you know how to do this via VBA. Post back if you need help there.

HS

Recipe Ingredients Table
---------------------------------
Recipe ID - Number - Long Interger
Ingredient ID - Text - Field Size 50
Main Ingredient - Yes/No
Ingredient Amount - Number - Long Interger
Unit Of Measure - Text - Field Size 50

Sample Data
------------------
1
Apples
Yes
6
Apples

Recipe Table
----------------------
Recipe ID - Number - Long Interger
Print This - Yes/No
Recipe Title - Text - Field Size 50
Instructions - Memo

1
Yes
Apple Cobler
Combine all ingredients


Hope this give you a better idea. Sorry for being too vague.

Tonye



tables
are it
to

 

[HSalim]

I hope you realize that you have to save the first query
then create a new query based on the cros-tab query created in step1

hs

Thank you so much for all the help you are giving me. I figured out how to
do the first part (creating the query) but I'm having trouble with the
select statement. I set up the query using the query design grid since I'm
more comfortable with it that with SQL. Here is what I set up.

RecipeID Ingredient ID The Value:
IngredientAmount
RecipeIngredients RecipeIngredients RecipeIngredients
Group By Group By Count
Row Heading Column Heading Value

I tried putting "Apples" under IngredientID and got the correct results.
Then I tried putting "Apples" and "Strawberries" under IngredientID and did
not get any results. There is one record that matches this criteria though.

The second part of your e-mail contained directions for creating a temp
table. Is this something I would need to do since since this database will
be run exclusively in Access? From what you said I didn't think so but I
just wanted to check.

Again, thanks for the time you are taking to help.

TH

ok TH,
As I suspected - each of your ingredients are listed in separate rows.

Here's a suggestion which I dearly hope you will adopt. Please avoid using
spaces in your
table and column names. Use Mixed case to distinguish CompoundNames

Back to our regularly scheduled programming.
Create a query - call it qryPivotIngredients
TRANSFORM Count(Ingredients.IngredientAmount) AS Quantity
SELECT Ingredients.RecipeId
FROM Ingredients
GROUP BY Ingredients.RecipeId
PIVOT Ingredients.IngredientID;

You can now use syntax similar to what you proposed

Select * from [qryPivotIngredients] Where [Apples]>0 AND [Strawberries]>0

There are a few problems with this approach.
1. You cannot use wild card searches because each ingredient is a column
2. You will have to use some means to ensure that the column names are
entered
exactly as theyare in the database - this is best achieved using
list/comboboxes.
3. There is a limit to the number of columns in a cross tab query -
It is either 256 or 1024 - not a lot for this table which could easily
exceed those numbers
. Further performace is inversely proportional to the number of ingredients
and rows in the table.
4. Cross Tab queries use proprietary extensions to SQL that are not easily
portable to other databases including SQL server.
If you are using Access as a client to other databases, your query will
run but
performance will be even more dismal
You probably dont care about this, but for the sake of those cowboy who
might see
this posting and shout "YeeeeeeHAW!", let this be a fair warning.

So what's a cowboy to do?

Use a temp table and popluate it using VBA using two passes - all you need
is a table
with one column - recipeID, so lets call it Results

(Well, first delete the temp table, incase it has any rows from a previous
operation)
Delete * from Results

'1. Get list of recipes with first item
insert into Results(recipeID)
select distinct RecipeID from RecipeIngredients where INgredientID like
"straw*"

' Delete all Recipes that do not match on other ingredients, because

this

is
an AND search
2. Delete REsults.* from Results
left join RecipeIngredients on results.recipeID =
RecipeIngredients.REcipeID
where RecipeIngredients.IngredientID = "apples" or
RecipeIngredients.IngredientID Like "Peac*"
and RecipeIngredients.IngredientID is null


I hope you know how to do this via VBA. Post back if you need help there.

HS




will
be think
the

get

it

see

if Apples
and

 

[HSalim]

Oh, and re temp tables in Part 2.
Parts 1 and 2 are two complete options so you dont have to do anything in
part2 if you dont want to.

Although I would personally implement part 2, you might find that Part1 is
in some ways simpler to use and maintain
and suits your needs for now.

HS

I hope you realize that you have to save the first query
then create a new query based on the cros-tab query created in step1

hs

Thank you so much for all the help you are giving me. I figured out how to
do the first part (creating the query) but I'm having trouble with the
select statement. I set up the query using the query design grid since I'm
more comfortable with it that with SQL. Here is what I set up.

RecipeID Ingredient ID The Value:
IngredientAmount
RecipeIngredients RecipeIngredients RecipeIngredients
Group By Group By Count
Row Heading Column Heading Value

I tried putting "Apples" under IngredientID and got the correct results.
Then I tried putting "Apples" and "Strawberries" under IngredientID and did
not get any results. There is one record that matches this criteria though.

The second part of your e-mail contained directions for creating a temp
table. Is this something I would need to do since since this database will
be run exclusively in Access? From what you said I didn't think so but I
just wanted to check.

Again, thanks for the time you are taking to help.

TH

ok TH,
As I suspected - each of your ingredients are listed in separate rows.

Here's a suggestion which I dearly hope you will adopt. Please avoid using
spaces in your
table and column names. Use Mixed case to distinguish CompoundNames

Back to our regularly scheduled programming.
Create a query - call it qryPivotIngredients
TRANSFORM Count(Ingredients.IngredientAmount) AS Quantity
SELECT Ingredients.RecipeId
FROM Ingredients
GROUP BY Ingredients.RecipeId
PIVOT Ingredients.IngredientID;

You can now use syntax similar to what you proposed

Select * from [qryPivotIngredients] Where [Apples]>0 AND [Strawberries]>0

There are a few problems with this approach.
1. You cannot use wild card searches because each ingredient is a column
2. You will have to use some means to ensure that the column names are
entered
exactly as theyare in the database - this is best achieved using
list/comboboxes.
3. There is a limit to the number of columns in a cross tab query -
It is either 256 or 1024 - not a lot for this table which could easily
exceed those numbers
. Further performace is inversely proportional to the number of ingredients
and rows in the table.
4. Cross Tab queries use proprietary extensions to SQL that are not easily
portable to other databases including SQL server.
If you are using Access as a client to other databases, your

query
will

run but
performance will be even more dismal
You probably dont care about this, but for the sake of those

cowboy
who

might see
this posting and shout "YeeeeeeHAW!", let this be a fair warning.

So what's a cowboy to do?

Use a temp table and popluate it using VBA using two passes - all you need
is a table
with one column - recipeID, so lets call it Results

(Well, first delete the temp table, incase it has any rows from a previous
operation)
Delete * from Results

'1. Get list of recipes with first item
insert into Results(recipeID)
select distinct RecipeID from RecipeIngredients where INgredientID like
"straw*"

' Delete all Recipes that do not match on other ingredients, because

this

is
an AND search
2. Delete REsults.* from Results
left join RecipeIngredients on results.recipeID =
RecipeIngredients.REcipeID
where RecipeIngredients.IngredientID = "apples" or
RecipeIngredients.IngredientID Like "Peac*"
and RecipeIngredients.IngredientID is null


I hope you know how to do this via VBA. Post back if you need help there.

HS





Recipe Ingredients Table
---------------------------------
Recipe ID - Number - Long Interger
Ingredient ID - Text - Field Size 50
Main Ingredient - Yes/No
Ingredient Amount - Number - Long Interger
Unit Of Measure - Text - Field Size 50

Sample Data
------------------
1
Apples
Yes
6
Apples

Recipe Table
----------------------
Recipe ID - Number - Long Interger
Print This - Yes/No
Recipe Title - Text - Field Size 50
Instructions - Memo

1
Yes
Apple Cobler
Combine all ingredients


Hope this give you a better idea. Sorry for being too vague.

Tonye



OK lets try this again.
The answer I'd like to see is:
Recipes (Table)
----------------------
ID - Number - AutoNumber
Description - text 50
...
Sample Data
1. Peach Cobbler, ...
2. Apple Strawberry Disaster, ...
3. Experiments with beer, curry powder and Chocolate (Bad!)

Instructions
----------------
RecipeID - foreign Key (Recipes.ID)
StepNumber - Number
Description - TExt 255

Sample Data
1, 1, blend peaches
1,2, Do something else
1,3, Do something else
3,1, My Kids idea of good food
3,2, Throw everything in
...

Ingredients
RecipeID
IngredientName
Quantity
Measure

Sample DAta
1,Peaches, 2, each
2,Apples, 3, Each
2,Strawberries 10, oz
3, Beer, 32, oz
3, Curry Powder, 1 tsp


Remember, we are NOT at standing by your side and looking over your
shoulder.
Give us the means to help you.

HS

There are two main tables. The first is Recipes; it holds information
about
the recipe that is required only once for each recipe - Instructions,
nutritional information, who the recipe came from, etc. The second
table
is
just for ingredients (text field) and their amounts since there will
be
more
than one ingredient for each recipe. The fields are joined by a
common
field - Recipe ID (a number). The Recipe ID fields in the two tables
are
in
a one to many relationship.

Currently, I am unable to get the resultset to return Apples and
Strawberries. I can only get it to sort on one ingredient. I think
the
reason for this is that there are more that one record in the
Ingredient
table for each recipe in the Recipe table. I don't know how to

get

it
to
look at the record for each recipe as a group and to be able to

see

if
that
group has two or three ingredients.

Thanks for helping.

Tonye


What are the columns(fields) and datatypes of your table?
And how are you currently getting your resultset to return Apples
and
Strawberries?

I think I know what a thing or two databases , but ...

HS



HS,

Thank you for answering. What do you mean by table
details? Also, you suggested using Apples OR
Strawberries. However, it needs to be Apples And
Strawberries. Part of the purpose of the database is to
be able to find recipes that contain a combination of
ingredients if need be. Any ideas?

Tonye


-----Original Message-----
TH,
It would help to have table details - saves long
explanations too

instead of using Apples And Strawberries, try using
Apples OR Strawberries

HS

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 three things:
1. Search based on just one ingredient
2 Search based on two ingredients so that the
recipe would have to
have
both ingredients to show up in the results
3. Search based on three ingredients so that the
recipe would have to
have all three ingredients to show up in the results.
Doing just one ingredient is easy. It's when I get to
two and three that
I
have a problem. The thing is there won't always be a
second and third
ingredient to search on. It will depend on how the
user wants to search.

I have the database set up with multiple tables. A
couple of these
include
one for all the details related to the recipe and one
for the ingredients
specific to the recipe. 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 can get
it
to where it shows recipes that have say apples or
strawberries. When I
put
these two on the same line to create an And criteria I
get no results even
though there are recipes fitting those criteria. Is it
looking for both of
these to be in the same record, same field? How do I
get it to look
through
all of the ingredients for a particular recipe so it
does what #2 and #3
above indicate?

Thank you for your time to anyone who is able to
answer. I just have no
idea how to do this.

Tonye








.

 

[TH]

Thanks. That works. Now I have only one more question. Which field I put
the search criteria will depend on which way the user decides to sort.
Also, as recipes are entered there will be more and more ways to sort. How
can I set it up to sort based on what the user enters? Is there a way that
I can set which field the search criteria goes into based on user selection?
It would be easy if you always entered "Apples" and "Strawberries".
Unfortuantely, it won't always be that way.

Tonye

I hope you realize that you have to save the first query
then create a new query based on the cros-tab query created in step1

hs

Thank you so much for all the help you are giving me. I figured out how to
do the first part (creating the query) but I'm having trouble with the
select statement. I set up the query using the query design grid since I'm
more comfortable with it that with SQL. Here is what I set up.

RecipeID Ingredient ID The Value:
IngredientAmount
RecipeIngredients RecipeIngredients RecipeIngredients
Group By Group By Count
Row Heading Column Heading Value

I tried putting "Apples" under IngredientID and got the correct results.
Then I tried putting "Apples" and "Strawberries" under IngredientID and did
not get any results. There is one record that matches this criteria though.

The second part of your e-mail contained directions for creating a temp
table. Is this something I would need to do since since this database will
be run exclusively in Access? From what you said I didn't think so but I
just wanted to check.

Again, thanks for the time you are taking to help.

TH

ok TH,
As I suspected - each of your ingredients are listed in separate rows.

Here's a suggestion which I dearly hope you will adopt. Please avoid using
spaces in your
table and column names. Use Mixed case to distinguish CompoundNames

Back to our regularly scheduled programming.
Create a query - call it qryPivotIngredients
TRANSFORM Count(Ingredients.IngredientAmount) AS Quantity
SELECT Ingredients.RecipeId
FROM Ingredients
GROUP BY Ingredients.RecipeId
PIVOT Ingredients.IngredientID;

You can now use syntax similar to what you proposed

Select * from [qryPivotIngredients] Where [Apples]>0 AND [Strawberries]>0

There are a few problems with this approach.
1. You cannot use wild card searches because each ingredient is a column
2. You will have to use some means to ensure that the column names are
entered
exactly as theyare in the database - this is best achieved using
list/comboboxes.
3. There is a limit to the number of columns in a cross tab query -
It is either 256 or 1024 - not a lot for this table which could easily
exceed those numbers
. Further performace is inversely proportional to the number of ingredients
and rows in the table.
4. Cross Tab queries use proprietary extensions to SQL that are not easily
portable to other databases including SQL server.
If you are using Access as a client to other databases, your

query
will

run but
performance will be even more dismal
You probably dont care about this, but for the sake of those

cowboy
who

might see
this posting and shout "YeeeeeeHAW!", let this be a fair warning.

So what's a cowboy to do?

Use a temp table and popluate it using VBA using two passes - all you need
is a table
with one column - recipeID, so lets call it Results

(Well, first delete the temp table, incase it has any rows from a previous
operation)
Delete * from Results

'1. Get list of recipes with first item
insert into Results(recipeID)
select distinct RecipeID from RecipeIngredients where INgredientID like
"straw*"

' Delete all Recipes that do not match on other ingredients, because

this

is
an AND search
2. Delete REsults.* from Results
left join RecipeIngredients on results.recipeID =
RecipeIngredients.REcipeID
where RecipeIngredients.IngredientID = "apples" or
RecipeIngredients.IngredientID Like "Peac*"
and RecipeIngredients.IngredientID is null


I hope you know how to do this via VBA. Post back if you need help there.

HS





Recipe Ingredients Table
---------------------------------
Recipe ID - Number - Long Interger
Ingredient ID - Text - Field Size 50
Main Ingredient - Yes/No
Ingredient Amount - Number - Long Interger
Unit Of Measure - Text - Field Size 50

Sample Data
------------------
1
Apples
Yes
6
Apples

Recipe Table
----------------------
Recipe ID - Number - Long Interger
Print This - Yes/No
Recipe Title - Text - Field Size 50
Instructions - Memo

1
Yes
Apple Cobler
Combine all ingredients


Hope this give you a better idea. Sorry for being too vague.

Tonye



OK lets try this again.
The answer I'd like to see is:
Recipes (Table)
----------------------
ID - Number - AutoNumber
Description - text 50
...
Sample Data
1. Peach Cobbler, ...
2. Apple Strawberry Disaster, ...
3. Experiments with beer, curry powder and Chocolate (Bad!)

Instructions
----------------
RecipeID - foreign Key (Recipes.ID)
StepNumber - Number
Description - TExt 255

Sample Data
1, 1, blend peaches
1,2, Do something else
1,3, Do something else
3,1, My Kids idea of good food
3,2, Throw everything in
...

Ingredients
RecipeID
IngredientName
Quantity
Measure

Sample DAta
1,Peaches, 2, each
2,Apples, 3, Each
2,Strawberries 10, oz
3, Beer, 32, oz
3, Curry Powder, 1 tsp


Remember, we are NOT at standing by your side and looking over your
shoulder.
Give us the means to help you.

HS

There are two main tables. The first is Recipes; it holds information
about
the recipe that is required only once for each recipe - Instructions,
nutritional information, who the recipe came from, etc. The second
table
is
just for ingredients (text field) and their amounts since there will
be
more
than one ingredient for each recipe. The fields are joined by a
common
field - Recipe ID (a number). The Recipe ID fields in the two tables
are
in
a one to many relationship.

Currently, I am unable to get the resultset to return Apples and
Strawberries. I can only get it to sort on one ingredient. I think
the
reason for this is that there are more that one record in the
Ingredient
table for each recipe in the Recipe table. I don't know how to

get

it
to
look at the record for each recipe as a group and to be able to

see

if
that
group has two or three ingredients.

Thanks for helping.

Tonye


What are the columns(fields) and datatypes of your table?
And how are you currently getting your resultset to return Apples
and
Strawberries?

I think I know what a thing or two databases , but ...

HS



HS,

Thank you for answering. What do you mean by table
details? Also, you suggested using Apples OR
Strawberries. However, it needs to be Apples And
Strawberries. Part of the purpose of the database is to
be able to find recipes that contain a combination of
ingredients if need be. Any ideas?

Tonye


-----Original Message-----
TH,
It would help to have table details - saves long
explanations too

instead of using Apples And Strawberries, try using
Apples OR Strawberries

HS

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 three things:
1. Search based on just one ingredient
2 Search based on two ingredients so that the
recipe would have to
have
both ingredients to show up in the results
3. Search based on three ingredients so that the
recipe would have to
have all three ingredients to show up in the results.
Doing just one ingredient is easy. It's when I get to
two and three that
I
have a problem. The thing is there won't always be a
second and third
ingredient to search on. It will depend on how the
user wants to search.

I have the database set up with multiple tables. A
couple of these
include
one for all the details related to the recipe and one
for the ingredients
specific to the recipe. 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 can get
it
to where it shows recipes that have say apples or
strawberries. When I
put
these two on the same line to create an And criteria I
get no results even
though there are recipes fitting those criteria. Is it
looking for both of
these to be in the same record, same field? How do I
get it to look
through
all of the ingredients for a particular recipe so it
does what #2 and #3
above indicate?

Thank you for your time to anyone who is able to
answer. I just have no
idea how to do this.

Tonye








.

 

[HSalim]

see, now you are getting all fancy and complicated.
Pretty soon, you'll be back asking for wild-card searches

By the time you begin coding these search options, it can get so unwieldy,
and unelegeant.

So, why don't you try option 2. Much easier in the long run, and as a bonus
you learn
to use VBA. C'mon, if you are up to it, I'll help you.

HS

Thanks. That works. Now I have only one more question. Which field I put
the search criteria will depend on which way the user decides to sort.
Also, as recipes are entered there will be more and more ways to sort. How
can I set it up to sort based on what the user enters? Is there a way that
I can set which field the search criteria goes into based on user selection?
It would be easy if you always entered "Apples" and "Strawberries".
Unfortuantely, it won't always be that way.

Tonye

I hope you realize that you have to save the first query
then create a new query based on the cros-tab query created in step1

hs

how
to since
I'm and
did

but

I

just wanted to check.

Again, thanks for the time you are taking to help.

TH

ok TH,
As I suspected - each of your ingredients are listed in separate rows.

Here's a suggestion which I dearly hope you will adopt. Please avoid
using
spaces in your
table and column names. Use Mixed case to distinguish CompoundNames

Back to our regularly scheduled programming.
Create a query - call it qryPivotIngredients
TRANSFORM Count(Ingredients.IngredientAmount) AS Quantity
SELECT Ingredients.RecipeId
FROM Ingredients
GROUP BY Ingredients.RecipeId
PIVOT Ingredients.IngredientID;

You can now use syntax similar to what you proposed

Select * from [qryPivotIngredients] Where [Apples]>0 AND [Strawberries]>0

There are a few problems with this approach.
1. You cannot use wild card searches because each ingredient is a column
2. You will have to use some means to ensure that the column names are
entered
exactly as theyare in the database - this is best achieved using
list/comboboxes.
3. There is a limit to the number of columns in a cross tab query -
It is either 256 or 1024 - not a lot for this table which could easily
exceed those numbers
. Further performace is inversely proportional to the number of
ingredients
and rows in the table.
4. Cross Tab queries use proprietary extensions to SQL that are not
easily
portable to other databases including SQL server.
If you are using Access as a client to other databases, your query
will
run but
performance will be even more dismal
You probably dont care about this, but for the sake of those cowboy
who
might see
this posting and shout "YeeeeeeHAW!", let this be a fair warning.

So what's a cowboy to do?

Use a temp table and popluate it using VBA using two passes - all

you
need

is a table
with one column - recipeID, so lets call it Results

(Well, first delete the temp table, incase it has any rows from a previous
operation)
Delete * from Results

'1. Get list of recipes with first item
insert into Results(recipeID)
select distinct RecipeID from RecipeIngredients where INgredientID like
"straw*"

' Delete all Recipes that do not match on other ingredients, because this
is
an AND search
2. Delete REsults.* from Results
left join RecipeIngredients on results.recipeID =
RecipeIngredients.REcipeID
where RecipeIngredients.IngredientID = "apples" or
RecipeIngredients.IngredientID Like "Peac*"
and RecipeIngredients.IngredientID is null


I hope you know how to do this via VBA. Post back if you need help there.

HS





Recipe Ingredients Table
---------------------------------
Recipe ID - Number - Long Interger
Ingredient ID - Text - Field Size 50
Main Ingredient - Yes/No
Ingredient Amount - Number - Long Interger
Unit Of Measure - Text - Field Size 50

Sample Data
------------------
1
Apples
Yes
6
Apples

Recipe Table
----------------------
Recipe ID - Number - Long Interger
Print This - Yes/No
Recipe Title - Text - Field Size 50
Instructions - Memo

1
Yes
Apple Cobler
Combine all ingredients


Hope this give you a better idea. Sorry for being too vague.

Tonye



OK lets try this again.
The answer I'd like to see is:
Recipes (Table)
----------------------
ID - Number - AutoNumber
Description - text 50
...
Sample Data
1. Peach Cobbler, ...
2. Apple Strawberry Disaster, ...
3. Experiments with beer, curry powder and Chocolate (Bad!)

Instructions
----------------
RecipeID - foreign Key (Recipes.ID)
StepNumber - Number
Description - TExt 255

Sample Data
1, 1, blend peaches
1,2, Do something else
1,3, Do something else
3,1, My Kids idea of good food
3,2, Throw everything in
...

Ingredients
RecipeID
IngredientName
Quantity
Measure

Sample DAta
1,Peaches, 2, each
2,Apples, 3, Each
2,Strawberries 10, oz
3, Beer, 32, oz
3, Curry Powder, 1 tsp


Remember, we are NOT at standing by your side and looking over your
shoulder.
Give us the means to help you.

HS

There are two main tables. The first is Recipes; it holds
information
about
the recipe that is required only once for each recipe -
Instructions,
nutritional information, who the recipe came from, etc. The second
table
is
just for ingredients (text field) and their amounts since

there
will

be
more
than one ingredient for each recipe. The fields are joined by a
common
field - Recipe ID (a number). The Recipe ID fields in the two
tables
are
in
a one to many relationship.

Currently, I am unable to get the resultset to return Apples and
Strawberries. I can only get it to sort on one ingredient. I think
the
reason for this is that there are more that one record in the
Ingredient
table for each recipe in the Recipe table. I don't know how

to
get

it
to
look at the record for each recipe as a group and to be able

to
see

if
that
group has two or three ingredients.

Thanks for helping.

Tonye


What are the columns(fields) and datatypes of your table?
And how are you currently getting your resultset to return Apples
and
Strawberries?

I think I know what a thing or two databases , but ...

HS



HS,

Thank you for answering. What do you mean by table
details? Also, you suggested using Apples OR
Strawberries. However, it needs to be Apples And
Strawberries. Part of the purpose of the database is to
be able to find recipes that contain a combination of
ingredients if need be. Any ideas?

Tonye


-----Original Message-----
TH,
It would help to have table details - saves long
explanations too

instead of using Apples And Strawberries, try using
Apples OR Strawberries

HS

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 three things:
1. Search based on just one ingredient
2 Search based on two ingredients so that the
recipe would have to
have
both ingredients to show up in the results
3. Search based on three ingredients so that the
recipe would have to
have all three ingredients to show up in the results.
Doing just one ingredient is easy. It's when I get to
two and three that
I
have a problem. The thing is there won't always be a
second and third
ingredient to search on. It will depend on how the
user wants to search.

I have the database set up with multiple tables. A
couple of these
include
one for all the details related to the recipe and one
for the ingredients
specific to the recipe. 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 can get
it
to where it shows recipes that have say apples or
strawberries. When I
put
these two on the same line to create an And criteria I
get no results even
though there are recipes fitting those criteria. Is it
looking for both of
these to be in the same record, same field? How do I
get it to look
through
all of the ingredients for a particular recipe so it
does what #2 and #3
above indicate?

Thank you for your time to anyone who is able to
answer. I just have no
idea how to do this.

Tonye








.

 

[TH]

Ok, I'll try it (gulp!). Thanks for the offer of help. Since this is the
only pending question for access I have it would be easier if you could
e-mail me at my e-mail address. Would that be ok? It's
[email protected] That way I can not worry about the newsgroups. I
check my e-mail address anyways. Thanks again.

Tonye

see, now you are getting all fancy and complicated.
Pretty soon, you'll be back asking for wild-card searches

By the time you begin coding these search options, it can get so unwieldy,
and unelegeant.

So, why don't you try option 2. Much easier in the long run, and as a bonus
you learn
to use VBA. C'mon, if you are up to it, I'll help you.

HS

Thanks. That works. Now I have only one more question. Which field I put
the search criteria will depend on which way the user decides to sort.
Also, as recipes are entered there will be more and more ways to sort. How
can I set it up to sort based on what the user enters? Is there a way that
I can set which field the search criteria goes into based on user selection?
It would be easy if you always entered "Apples" and "Strawberries".
Unfortuantely, it won't always be that way.

Tonye

I hope you realize that you have to save the first query
then create a new query based on the cros-tab query created in step1

hs

Thank you so much for all the help you are giving me. I figured out how
to
do the first part (creating the query) but I'm having trouble with the
select statement. I set up the query using the query design grid since
I'm
more comfortable with it that with SQL. Here is what I set up.

RecipeID Ingredient ID The Value:
IngredientAmount
RecipeIngredients RecipeIngredients RecipeIngredients
Group By Group By Count
Row Heading Column Heading Value

I tried putting "Apples" under IngredientID and got the correct results.
Then I tried putting "Apples" and "Strawberries" under IngredientID and
did
not get any results. There is one record that matches this criteria
though.

The second part of your e-mail contained directions for creating a temp
table. Is this something I would need to do since since this database
will
be run exclusively in Access? From what you said I didn't think so

but

I
just wanted to check.

Again, thanks for the time you are taking to help.

TH

ok TH,
As I suspected - each of your ingredients are listed in separate rows.

Here's a suggestion which I dearly hope you will adopt. Please avoid
using
spaces in your
table and column names. Use Mixed case to distinguish CompoundNames

Back to our regularly scheduled programming.
Create a query - call it qryPivotIngredients
TRANSFORM Count(Ingredients.IngredientAmount) AS Quantity
SELECT Ingredients.RecipeId
FROM Ingredients
GROUP BY Ingredients.RecipeId
PIVOT Ingredients.IngredientID;

You can now use syntax similar to what you proposed

Select * from [qryPivotIngredients] Where [Apples]>0 AND
[Strawberries]>0

There are a few problems with this approach.
1. You cannot use wild card searches because each ingredient is a column
2. You will have to use some means to ensure that the column names are
entered
exactly as theyare in the database - this is best achieved using
list/comboboxes.
3. There is a limit to the number of columns in a cross tab query -
It is either 256 or 1024 - not a lot for this table which could
easily
exceed those numbers
. Further performace is inversely proportional to the number of
ingredients
and rows in the table.
4. Cross Tab queries use proprietary extensions to SQL that are not
easily
portable to other databases including SQL server.
If you are using Access as a client to other databases, your query
will
run but
performance will be even more dismal
You probably dont care about this, but for the sake of those cowboy
who
might see
this posting and shout "YeeeeeeHAW!", let this be a fair warning.

So what's a cowboy to do?

Use a temp table and popluate it using VBA using two passes - all you
need
is a table
with one column - recipeID, so lets call it Results

(Well, first delete the temp table, incase it has any rows from a
previous
operation)
Delete * from Results

'1. Get list of recipes with first item
insert into Results(recipeID)
select distinct RecipeID from RecipeIngredients where

INgredientID
like

"straw*"

' Delete all Recipes that do not match on other ingredients, because
this
is
an AND search
2. Delete REsults.* from Results
left join RecipeIngredients on results.recipeID =
RecipeIngredients.REcipeID
where RecipeIngredients.IngredientID = "apples" or
RecipeIngredients.IngredientID Like "Peac*"
and RecipeIngredients.IngredientID is null


I hope you know how to do this via VBA. Post back if you need help
there.

HS





Recipe Ingredients Table
---------------------------------
Recipe ID - Number - Long Interger
Ingredient ID - Text - Field Size 50
Main Ingredient - Yes/No
Ingredient Amount - Number - Long Interger
Unit Of Measure - Text - Field Size 50

Sample Data
------------------
1
Apples
Yes
6
Apples

Recipe Table
----------------------
Recipe ID - Number - Long Interger
Print This - Yes/No
Recipe Title - Text - Field Size 50
Instructions - Memo

1
Yes
Apple Cobler
Combine all ingredients


Hope this give you a better idea. Sorry for being too vague.

Tonye



OK lets try this again.
The answer I'd like to see is:
Recipes (Table)
----------------------
ID - Number - AutoNumber
Description - text 50
...
Sample Data
1. Peach Cobbler, ...
2. Apple Strawberry Disaster, ...
3. Experiments with beer, curry powder and Chocolate (Bad!)

Instructions
----------------
RecipeID - foreign Key (Recipes.ID)
StepNumber - Number
Description - TExt 255

Sample Data
1, 1, blend peaches
1,2, Do something else
1,3, Do something else
3,1, My Kids idea of good food
3,2, Throw everything in
...

Ingredients
RecipeID
IngredientName
Quantity
Measure

Sample DAta
1,Peaches, 2, each
2,Apples, 3, Each
2,Strawberries 10, oz
3, Beer, 32, oz
3, Curry Powder, 1 tsp


Remember, we are NOT at standing by your side and looking over your
shoulder.
Give us the means to help you.

HS

There are two main tables. The first is Recipes; it holds
information
about
the recipe that is required only once for each recipe -
Instructions,
nutritional information, who the recipe came from, etc. The
second
table
is
just for ingredients (text field) and their amounts since there
will
be
more
than one ingredient for each recipe. The fields are joined

by

a