Is embedded SELECT statement possible in MS Access (2013)?

[magnusstefan]

Example from a Recipe Database when using two tables for an INNER JOIN:
1) Recipe_Classes with fields RecipeClassID and RecipeClassDescription


2) Recipes with fields RecipeTitle and Preparation

I am here trying to use an embedded SELECT statement for filtering as a part of INNER JOIN. The purpose is to show only recipes in the categories "Main course" or Dessert", but instead of a result set i got: "State parameter value RC.Classname".

Here is the code:

SELECT R.RecipeTitle, R.Preparation, RCFiltered.ClassName
FROM (SELECT RecipeClassID, RecipeClassDescription AS ClassName FROM Recipe_Classes AS RC WHERE RC.ClassName = 'Main course' OR RC.ClassName = 'Dessert') AS RCFiltered
INNER JOIN Recipes AS R 
ON RCFiltered.RecipeClassID = R.RecipeClassID;

This is not possible in Access, or?

[orange]

Yes possible. This construct is called a subquery.

See Allen Browne subquery
more on subquery

Good luck.

[magnusstefan]

Excellent!

I will check this up.

Thank you!