Subqueries in Access SQL: Queries Within Queries
Subqueries let you use the result of one query inside another, enabling complex filtering and calculations that are impossible with simple JOINs.
A subquery is a SELECT statement nested inside another SQL statement. It lets you use the result of one query as a value, a list, or a filter condition in another query. Subqueries solve problems that are difficult or impossible to express with simple JOINs.
Types of Subqueries
Scalar Subquery
Returns a single value and can be used anywhere a value is expected:
SELECT ProductName, UnitPrice,
(SELECT Avg(UnitPrice) FROM Products) AS AvgPrice,
UnitPrice - (SELECT Avg(UnitPrice) FROM Products) AS DiffFromAvg
FROM Products;
This shows each product's price alongside the average price and the difference.
Subquery in WHERE with IN
Returns a list of values and filters the outer query to records matching that list:
-- Find customers who have placed at least one order
SELECT CustomerID, CustomerName
FROM Customers
WHERE CustomerID IN (SELECT DISTINCT CustomerID FROM Orders);
-- Find customers who have NEVER placed an order
SELECT CustomerID, CustomerName
FROM Customers
WHERE CustomerID NOT IN (SELECT DISTINCT CustomerID FROM Orders);
The NOT IN pattern is particularly useful — it is the cleanest way to find records in one table that have no matching records in another.
Subquery in WHERE with EXISTS
EXISTS returns True if the subquery returns any rows. It is often faster than IN for large datasets because it stops as soon as it finds the first match:
-- Find customers with at least one order over $1000
SELECT CustomerID, CustomerName
FROM Customers AS c
WHERE EXISTS (
SELECT 1 FROM Orders AS o
WHERE o.CustomerID = c.CustomerID
AND o.OrderTotal > 1000
);
Note that the subquery references c.CustomerID from the outer query — this is a correlated subquery (see below).
Subquery in FROM
A subquery in the FROM clause creates a virtual table that the outer query can select from:
SELECT CustomerID, TotalOrders, TotalRevenue
FROM (
SELECT CustomerID,
Count(OrderID) AS TotalOrders,
Sum(OrderTotal) AS TotalRevenue
FROM Orders
GROUP BY CustomerID
) AS OrderSummary
WHERE TotalRevenue > 5000;
In Access, subqueries in the FROM clause must be given an alias (here: AS OrderSummary).
Correlated Subqueries
A correlated subquery references a column from the outer query. It is re-evaluated for every row in the outer query, which makes it powerful but potentially slow.
Example: Find the most recent order for each customer:
SELECT c.CustomerName, o.OrderDate, o.OrderTotal
FROM Customers AS c
INNER JOIN Orders AS o ON c.CustomerID = o.CustomerID
WHERE o.OrderDate = (
SELECT Max(OrderDate)
FROM Orders AS o2
WHERE o2.CustomerID = c.CustomerID
);
The subquery finds the maximum order date for each customer, and the outer query returns only the order that matches that date.
Finding Duplicates with Subqueries
-- Find all records where the email appears more than once
SELECT CustomerID, Email
FROM Customers
WHERE Email IN (
SELECT Email
FROM Customers
GROUP BY Email
HAVING Count(*) > 1
)
ORDER BY Email;
Top N Per Group
Finding the top N records within each group is a classic subquery problem:
-- Find the top 3 orders by amount for each customer
SELECT o.CustomerID, o.OrderID, o.OrderTotal
FROM Orders AS o
WHERE o.OrderID IN (
SELECT TOP 3 OrderID
FROM Orders AS o2
WHERE o2.CustomerID = o.CustomerID
ORDER BY OrderTotal DESC
)
ORDER BY o.CustomerID, o.OrderTotal DESC;
Subqueries vs. JOINs
Both subqueries and JOINs can solve many of the same problems. General guidelines:
Use a JOIN when:
- You need columns from both tables in the result
- Performance is critical (JOINs are usually faster)
- The relationship is straightforward
Use a subquery when:
- You need
NOT IN/NOT EXISTS(finding non-matching records) - You need a scalar value from another table
- The logic is clearer as a subquery
- You need to filter on an aggregate (HAVING in a subquery)
Performance Considerations
Correlated subqueries can be slow because they execute once per row in the outer query. For large tables, consider:
- Rewrite as a JOIN — often faster for correlated subqueries
- Create a saved query — Access can optimize a saved query better than an inline subquery
- Use a make-table query — materialize the subquery result into a temp table
Subqueries in Access: Syntax Notes
Access SQL supports subqueries in WHERE, FROM, and SELECT clauses. A few Access-specific notes:
- Subqueries in FROM must have an alias:
(SELECT ...) AS alias - Access does not support
WITH(Common Table Expressions) — use saved queries instead - Very deeply nested subqueries can cause "Query is too complex" errors — break them into saved queries
Conclusion
Subqueries are one of the most powerful tools in SQL, enabling patterns — like finding non-matching records, top-N per group, and scalar lookups — that are difficult to express any other way. Master the IN, NOT IN, EXISTS, and scalar subquery patterns and you will be able to answer complex data questions that would otherwise require multiple queries and manual work.
Work With Access Evolved
If you found this article useful, Access Evolved specializes in exactly this kind of work — from hands-on Microsoft Access support to full database modernization projects.
Building complex subqueries in Access? Schedule a free call and get expert help optimizing your query logic.
Thinking about migrating?
Ready to move beyond Microsoft Access?
Access is a great starting point, but growing businesses often hit its limits. Anthony Passeri specializes in migrating Access databases to modern platforms — Power Platform, Power Apps, SQL Server, Azure, and more — so you keep your data and workflows without the headaches.
Written by
Anthony Passeri, MBA
Access Expert and Editor
Bridging the gap between Access and the Future
Explore Topics
Keep reading
More on Queries & SQL
UNION Queries in Access: Combining Data from Multiple Sources
UNION queries stack results from multiple SELECT statements into a single result set. Learn when and how to use them effectively in Microsoft Access.
Parameter Queries in Access: Ask Users for Input at Runtime
Parameter queries prompt users for input when they run, making your queries flexible and reusable without any VBA code. Here is everything you need to know.
Totals Queries in Access: GROUP BY, COUNT, SUM, and More
Totals queries aggregate your data into summaries — counts, sums, averages, and more. Learn how to build them correctly and avoid the common mistakes.