A common requirement in Microsoft Access is to write a subquery, often when there is a requirement to return rows from one table along with aggregate data from another table e.g. Supplier names along with a count of the number of orders places with each one.
A common approach to this is writing one query, saving it as a query object, then writing a second query and adding the first query as one of the sources.
That does work, but can be difficult to maintain, if you or someone else, edits (or deletes) the first query, that’s going to break the second query. So you need something - a naming convention, or some kind of source code control which will keep track of all those relationships between different query objects.
There is an easier way - embed the subquery into a single query object.
As an example look at this query, it could be almost any query viewed as SQL
SELECT Table1.column2, Count(Table2.Column2) AS CountOfSomething
FROM Table1 LEFT JOIN Table2 ON Table1.Column_ID = Table2.Column_ID
GROUP BY Table1.column2
ORDER BY Table1.column2
To make this a subquery, add the parts shown in bold below:
SELECT * FROM (
SELECT Table1.column2, Count(Table2.Column2) AS CountOfSomething
FROM Table1 LEFT JOIN Table2 ON Table1.Column_ID = Table2.Column_ID
GROUP BY Table1.column2
ORDER BY Table1.column2) AS MySubQuery1
You can now return to design view and you will see MySubQuery1 as an object just like any other Table/Query, you can then build upon that and save the entire expression into a single Query object.
“It is important to remember that we all have magic inside us” ~ Joanne Kathleen Rowling (author of Harry Potter)
Related:
NULL values - Dealing with NULL values.
Number Data Types - Access data Types.