[thelist] Nested SQL question

[Matthew Walker]

Hiya,

I have two queries for an application that finds recipes you can make with
the ingredients you have onhand.

One query builds a list returning a RecipeID for each missing ingredient (so
a recipe with two missing ingredients would appear on the list twice). The
other query takes this result and provides a list of recipes ordered by the
number of missing ingredients (i.e. no missing ingredients first).

MissingIngredients:

SELECT Recipes_Ingredients.RecipeID
FROM Recipes_Ingredients LEFT JOIN Users_Ingredients ON
Recipes_Ingredients.IngredientID = Users_Ingredients.IngredientID
WHERE (((Users_Ingredients.IngredientID) Is Null));

Recipes:

SELECT Count([MissingIngredients].[RecipeID]) AS CountMissingIngredients,
[Recipes].[RecipeID], [Recipes].[RecipeName]
FROM MissingIngredients RIGHT JOIN Recipes ON
[MissingIngredients].[RecipeID]=[Recipes].[RecipeID]
GROUP BY [Recipes].[RecipeID], [Recipes].[RecipeName]
ORDER BY Count([MissingIngredients].[RecipeID]), [Recipes].[RecipeName];

OK so that works. I just have two queries in Access with one calling the
other. But I want to be able to specify a UserID which will mean the first
query has to be dynamic, constructed by the server. So I guess I need to
combine these two queries into one nested query. How do I do that? I just
don't get it.

Thank you,
Matt.