one basic solution uses three tables:
tblRecipes
RecipeID (primary key)
RecipeName
TotalIngredients (a number data type, Byte should be big enough)
tblIngredients
IngredientID (primary key)
IngredientName
OnHand (Yes/No field)
tblRecipeIngredients
RecipeIngredientID (primary key)
RecipeID (foreign key from tblRecipes)
IngredientID (foreign key from tblIngredients)
after building the tables, setting relationships on the matching
primary/foreign key fields, and entering data (leave the OnHand field blank
in each record in tblIngredients), you can build a form based on
tblIngredients. the purpose is to allow you to put checkmarks next to your
ingredients currently on hand. if you want to be able to clear the
checkmarks after each use, create a command button that runs an Update
query, as
UPDATE tblIngredients SET tblIngredients.OnHand = False;
create another query (i called it qryIngredientCount), as
SELECT tblRecipeIngredients.RecipeID,
Count(tblRecipeIngredients.RecipeIngredientID) AS CountOfRecipeIngredientID
FROM tblRecipeIngredients LEFT JOIN tblIngredients ON
tblRecipeIngredients.IngredientID = tblIngredients.IngredientID
WHERE (((tblIngredients.OnHand)=True))
GROUP BY tblRecipeIngredients.RecipeID;
create a third query (i called it qryAvailableRecipes), which is based
partly on qryIngredientCount, as
SELECT tblRecipes.RecipeID, tblRecipes.RecipeName
FROM tblRecipes INNER JOIN qryIngredientCount ON (tblRecipes.RecipeID =
qryIngredientCount.RecipeID) AND (tblRecipes.TotalIngredients =
qryIngredientCount.CountOfRecipeIngredientID);
it's not too pretty, but it does what you ask: it returns a list of the
recipes that contain only ingredients that you've indicated as "on hand".
should be enough to get you started.
hth
