Subqueries in Access SQL with IN, EXISTS, and Correlation

Some data questions are easiest to express as a query inside another query: customers who have ordered, products that have never sold, orders containing a high-quantity line, or records whose value exceeds a related average. Subqueries in Access SQL let the outer query ask those existence and membership questions directly.

This lesson focuses on IN, EXISTS, NOT EXISTS, and correlated subqueries. It also explains when a join is clearer, why Null can make NOT IN surprising, and how indexes on correlation fields affect performance.

The examples avoid scalar subqueries in the SELECT list and concentrate on patterns that are practical in Access filters. Each query is tested first as a saved SELECT query and then opened from VBA with an explicit parameter.

What this lesson adds to the Access SQL learning path

Subqueries belong after the core JOIN lesson because they solve related-table questions with a different mental model. A join combines rows into one result; EXISTS often keeps the outer row and merely asks whether a matching inner row is present.

Choose the form according to intent. Use IN when the inner query returns a clear set of comparable values. Use EXISTS when only presence matters. Use NOT EXISTS to locate missing related data. Use a correlated subquery when the inner test must change for each outer row.

Prerequisites and sample data model

The examples use Customers, Orders, OrderDetails, Products, and Categories. Customers has CustomerID as its primary key. Orders stores OrderID, CustomerID, OrderDate, and a status field. OrderDetails stores OrderID, ProductID, Quantity, and UnitPrice. Products stores ProductID, ProductName, and CategoryID. Primary and foreign keys should have compatible data types, and the join fields should be indexed when the tables become large.

Before running the examples, create a backup and confirm that table and field names match your database. If your names contain spaces, surround them with square brackets. Start with a SELECT query that returns a small result, then add grouping, parameters, or other advanced clauses one at a time.

Core syntax and mental model

A subquery is a SELECT statement nested inside another statement. IN compares a value with a returned list, EXISTS checks whether at least one related row is found, NOT EXISTS finds missing relationships, and a correlated subquery references the current row of the outer query.

SELECT CustomerID, CustomerName
FROM Customers
WHERE CustomerID IN
    (SELECT CustomerID
     FROM Orders
     WHERE OrderDate >= #2026-01-01#);

Read the statement from the logical data flow rather than only from top to bottom: identify source rows, apply row-level filters, combine or group data where required, calculate the output, apply result-level filters, and finally sort the rows. This mental model helps explain why moving a condition between WHERE and another clause can change the result.

When to use this technique

• Customers with at least one order in a period
• Products that have never appeared in OrderDetails
• Orders containing a line above a quantity threshold
• Employees whose records match a related status table
• Finding orphaned records with NOT EXISTS

A practical query with business rules

The business rule in the advanced example selects orders containing at least one line whose quantity reaches a parameter, while excluding any order that already has a return record.

PARAMETERS [pMinimumQuantity] Long;
SELECT O.OrderID,
       O.CustomerID,
       O.OrderDate
FROM Orders AS O
WHERE EXISTS
    (SELECT *
     FROM OrderDetails AS OD
     WHERE OD.OrderID = O.OrderID
       AND OD.Quantity >= [pMinimumQuantity])
  AND NOT EXISTS
    (SELECT *
     FROM Returns AS R
     WHERE R.OrderID = O.OrderID)
ORDER BY O.OrderDate DESC;

The correlation OD.OrderID = O.OrderID links the inner check to the current outer order. The second NOT EXISTS applies the same pattern to Returns. Because only existence matters, SELECT * inside EXISTS is descriptive and does not return those inner columns to the final result.

Executing the query from VBA

For reusable automation, save the SQL as a named query and execute it through DAO. Explicit parameters are preferable to string concatenation because Access knows their types before it builds the execution plan. The following procedure demonstrates the pattern; adjust the QueryDef name and parameters to match the saved query used in this lesson.

Option Explicit

Public Sub ShowQualifiedOrders()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rs As DAO.Recordset

    On Error GoTo CleanFail

    Set db = CurrentDb
    Set qdf = db.QueryDefs("qryQualifiedOrdersBySubquery")
    qdf.Parameters("pMinimumQuantity") = 10
    Set rs = qdf.OpenRecordset(dbOpenSnapshot)

    Do While Not rs.EOF
        Debug.Print rs!OrderID, rs!CustomerID, rs!OrderDate
        rs.MoveNext
    Loop

CleanExit:
    If Not rs Is Nothing Then rs.Close
    Set rs = Nothing
    Set qdf = Nothing
    Set db = Nothing
    Exit Sub

CleanFail:
    Debug.Print Err.Number, Err.Description
    Resume CleanExit
End Sub

The procedure opens a snapshot because the example reads results. Use an action-query execution method only for INSERT, UPDATE, DELETE, or make-table operations, and always inspect RecordsAffected. Close objects in a cleanup section so that an error does not leave a recordset open.

How to verify the result

1. Run the underlying source query and note the number of rows before applying the advanced clause.
2. Calculate one small case manually and compare it with the query output.
3. Test a parameter range that returns no rows and confirm that the calling code handles it.
4. Add a duplicate or Null value intentionally and observe whether the result matches the documented rule.
5. Compare the saved QueryDef result with the VBA Recordset row count.
6. Record the final field names and data types expected by the report or export routine.

Common errors and fixes

Performance, safety, and maintainability

A reliable subqueries in Access SQL workflow starts with a small, verified data set. Create a copy of the database, add a few rows whose values are easy to recognize, and run the query in read-only form before connecting it to a form, report, or VBA procedure. This habit separates SQL design mistakes from automation mistakes and makes every later test easier to explain.

Access SQL is close to standard SQL, but it has its own expression service, date delimiters, wildcard behavior, parameter handling, and query designer. When you work with subqueries in Access SQL, test the statement in the Access query window first. After the result is correct, move the same logic into a saved QueryDef or a VBA string. That sequence reduces quoting errors and exposes missing parameters early.

Use stable field names and explicit aliases. An alias should describe the meaning of a calculated column rather than repeat the expression. Clear aliases make reports easier to bind, simplify VBA recordset code, and prevent users from seeing names such as Expr1000. If a field name contains spaces or conflicts with a reserved word, wrap it in square brackets.

Null values require deliberate treatment. Aggregate functions, comparisons, joins, and expressions do not all handle Null in the same way. Decide whether Null means unknown, not applicable, or zero in the business rule. Use Nz only when replacing Null is semantically correct; otherwise you may hide missing data and produce totals that look precise but are not.

For production work, separate input validation from query execution. Validate dates, identifiers, and numeric limits before assigning them to parameters. Do not concatenate user text into SQL when a parameter is available. Parameterization improves type handling and reduces the risk of malformed statements, especially when apostrophes, locale-specific dates, or decimal separators are involved.

Performance should be measured with representative data. A query that is instant with twenty rows may become slow with hundreds of thousands. Index fields used repeatedly in joins and selective filters, avoid wrapping indexed fields in functions when a range comparison can be used, and return only the columns required by the next step. These choices matter more than cosmetic changes to SQL formatting.

A good test plan includes normal data, boundary values, Nulls, duplicates, and an empty result. Check whether the output still makes sense when no record matches, when one group contains a single row, and when two rows share the same sort value. Record the expected output before changing the query so that later refactoring can be compared against a known baseline.

Saved queries are useful documentation. Give each QueryDef a descriptive name, add a short comment in the surrounding VBA module, and keep the query focused on one responsibility. A saved query can be opened directly for troubleshooting, reused by forms and reports, and parameterized without building long SQL strings in code.

When a query changes data or feeds a critical report, log its inputs and the number of affected or returned rows. The log does not need to store sensitive values. A timestamp, query name, parameter summary, and row count are usually enough to diagnose unexpected behavior while preserving privacy.

Treat subqueries in Access SQL as part of a larger data pipeline. The query should have a clear input, a predictable output shape, and a documented consumer. Knowing whether the result will populate a chart, drive an update, export to Excel, or appear in a report determines the best aliases, ordering, Null policy, and level of detail.

Guided practice

Build the lesson in three passes. In pass one, return raw rows with only the required joins and filters. In pass two, introduce the main technique and verify the result with a small sample. In pass three, add parameters, aliases, sorting, and the VBA wrapper. Save each pass under a temporary name so you can compare the outputs and identify the exact change that caused a difference.

For a second exercise, change one business rule without rewriting the entire query. Examples include changing the reporting period, excluding cancelled orders, selecting a different category, or switching between detail and summary output. A well-structured query should allow this change in one filter, parameter, or calculated expression rather than in several disconnected places.

Finally, inspect the query from the perspective of another developer. Can they identify the input tables, parameters, output columns, and expected row granularity without opening every form that uses it? Rename unclear aliases, remove unused columns, and add a brief module comment describing the assumptions. This review often prevents more defects than another round of cosmetic formatting.

Related lessons in this series

This lesson follows the core SELECT, WHERE, JOIN, and data-editing topics. The related-link metadata also records the next planned lessons so the site can connect the series after publication. Review the existing guide on updating and deleting Access data with VBA when the workflow eventually changes records.

Conclusion

After completing the lesson, you should be able to translate a membership or missing-relationship question into IN or EXISTS, recognize a correlated reference, avoid Null traps, and execute the saved query safely from VBA.

The most dependable approach is incremental: prove the SQL with controlled data, declare parameter types, verify Null and duplicate behavior, and only then automate the query. That workflow makes the result easier to trust and easier to maintain when tables, forms, or reporting requirements change.