multiple ingredient search in historical recipe database

[Anne S]

I have a database of historical recipes with a subdatasheet listing every
ingredient for each recipe title. I want to produce a query that will return
all the recipe titles that require a particular combination of ingredients.
Somehow the way to do this eludes me! I could search the subdatasheet for,
say, three specific ingredients and return the related recipe titles, but
this does not tell me which recipe titles contain all three.

I can manage select queries and cross-tab queries - should I be looking at
something more exotic to achieve my aims? Suggestions welcome!

 

[KARL DEWEY]

Use this for criteria on the ingredient field --
Like "*" & [ingredient 1] & "*" AND Like "*" & [ingredient 2] & "*" AND
Like "*" & [ingredient 3] & "*"

 

[John Spencer]

It kind of depends on the structure of your database. I am guessing that you
have a table of recipeIngredients linked to a table of Recipes.


SELECT *
FROM Recipes INNER JOIN RecipeIngredients as RI
On Recipes.RecipeID= RI.RecipeID
WHERE RecipeID in
(SELECT Tmp.RecipeID
FROM RecipeIngredients as Tmp
WHERE Tmp.IngredientName IN ("Salt","Pepper","Cream")
GROUP BY Tmp.RecipeID
HAVING Count(*) = 3)

That works as long as you don't have the same ingredient listed twice for a
recipe. If that is the case you would need to get a unique count of the
ingredients. That is a bit more complex to do. If you (1) understand and can
use the above AND (2) need to solve the problem of duplicate ingredients, post
back for the more complex solution.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Anne S]

Hi, Much appreciate suggestions so far.
I have 3 linked tables, because of the need to 'defuzz' (sorry not sure of
the technical term the varied ingredient names in historical recipes to
indicate the same 'source' ingredient:
RECEIPTS INGREDIENTSinReceipts INGREDIENTSIngredientNametoSourceName
[Receipt title] .. [Receipt ID] ..[Ingredient name]
[Receipt id] .. [Ingredient name] .. [Source species]

My approach to find receipt titles with same 3 specific ingredients has been
to use a select query then a crosstab to display the numbers of one or more
of the selected ingredients. Not ideal as all the recipe titles are listed. I
have tried the suggested SQL route but keep getting syntax errors and think
it might be to do with the linking table. Perhaps I need some pointers to
'Introduction to SQL and queries' to better understand the way it should
work?

These were my original attempts:
Select Query
SELECT INGREDIENTSIngredientNametoSourceName.[Source species],
INGREDIENTSIngredientNametoSourceName.[Source species],
INGREDIENTSIngredientNametoSourceName.[Source species], RECEIPTS.[receipt
title], RECEIPTS.collectiontitle
FROM INGREDIENTSIngredientNametoSourceName INNER JOIN (RECEIPTS INNER JOIN
INGREDIENTSinReceipts ON RECEIPTS.[Receipt id] =
INGREDIENTSinReceipts.[Receipt ID]) ON
INGREDIENTSIngredientNametoSourceName.[Ingredient name] =
INGREDIENTSinReceipts.[Ingredient name]
WHERE (((INGREDIENTSIngredientNametoSourceName.[Source
species])="pennyroyal")) OR (((INGREDIENTSIngredientNametoSourceName.[Source
species])="marjoram")) OR (((INGREDIENTSIngredientNametoSourceName.[Source
species])="betony"));
Cross tab query

TRANSFORM Count([20080505select3ingredientsforRXtitle].[Source species]) AS
[CountOfSource species]
SELECT [20080505select3ingredientsforRXtitle].collectiontitle,
[20080505select3ingredientsforRXtitle].[receipt title],
Count([20080505select3ingredientsforRXtitle].[Source species]) AS [Count of]
FROM 20080505select3ingredientsforRXtitle
GROUP BY [20080505select3ingredientsforRXtitle].collectiontitle,
[20080505select3ingredientsforRXtitle].[receipt title]
PIVOT [20080505select3ingredientsforRXtitle].[Source species];

Any further suggestions most welcome! If I can understand this better then
I can match recipes from different collections that have common ingredients,
very useful in research establishing where the recipes came from.