UNION and UNION ALL Queries in Access SQL

Sometimes two tables describe the same kind of output even though they store different business events. Customers and suppliers can form one contact list; sales and returns can form one activity stream; current and archive tables can form one reporting source. UNION queries in Access SQL combine those compatible results vertically.

This lesson explains column alignment, data-type compatibility, aliases, duplicate handling, final ordering, and parameters. It also shows why UNION ALL should be the default when duplicate removal is not a business requirement.

UNION differs from JOIN. A join adds columns by matching related rows, while UNION adds rows from separate SELECT statements. Confusing these operations leads to wide results when a long consolidated list was required, or duplicate rows when a relationship should have been joined.

What this lesson adds to the Access SQL learning path

The lesson follows aggregation and subqueries because reporting often needs one normalized stream before totals or existence checks are applied. Once multiple sources expose the same output schema, downstream forms, reports, exports, and VBA code can treat them consistently.

Design the output contract first: column count, order, meaning, data type, and aliases. The names from the first SELECT become the field names of the final result, so make those aliases descriptive and stable.

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

UNION stacks rows from compatible SELECT statements. Every branch must return the same number of columns in the same order, with compatible data types. UNION removes duplicate rows, while UNION ALL preserves them and is usually faster. ORDER BY belongs once at the end.

SELECT CustomerID AS EntityID,
       CustomerName AS EntityName,
       'Customer' AS EntityType
FROM Customers
UNION ALL
SELECT SupplierID AS EntityID,
       SupplierName AS EntityName,
       'Supplier' AS EntityType
FROM Suppliers
ORDER BY EntityName;

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

• Combining current and archive records
• Creating one contact list from customers and suppliers
• Merging sales and returns into a signed ledger
• Combining manual and imported transactions
• Building a shared source for reports and exports

A practical query with business rules

The advanced example builds a signed activity ledger. Sales are positive amounts and returns are negative amounts. Both branches expose ActivityID, ActivityDate, PartyID, Amount, and ActivityType, allowing a report to sort and total one consistent stream.

PARAMETERS [pStartDate] DateTime, [pEndDate] DateTime;
SELECT O.OrderID AS ActivityID,
       O.OrderDate AS ActivityDate,
       O.CustomerID AS PartyID,
       O.TotalAmount AS Amount,
       'Sale' AS ActivityType
FROM Orders AS O
WHERE O.OrderDate Between [pStartDate] And [pEndDate]
UNION ALL
SELECT R.ReturnID AS ActivityID,
       R.ReturnDate AS ActivityDate,
       R.CustomerID AS PartyID,
       -R.ReturnAmount AS Amount,
       'Return' AS ActivityType
FROM Returns AS R
WHERE R.ReturnDate Between [pStartDate] And [pEndDate]
ORDER BY ActivityDate, ActivityID;

The date parameters appear in both branches. Each branch filters its own date field, and ORDER BY appears once after the final SELECT. UNION ALL preserves separate events even when all displayed values happen to be identical.

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 ExportCombinedActivity()
    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("qryCombinedSalesAndReturns")
    qdf.Parameters("pStartDate") = DateSerial(2026, 1, 1)
    qdf.Parameters("pEndDate") = DateSerial(2026, 12, 31)
    Set rs = qdf.OpenRecordset(dbOpenSnapshot)

    Do While Not rs.EOF
        Debug.Print rs!ActivityDate, rs!ActivityType, rs!Amount
        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 UNION queries 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 UNION queries 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 UNION queries 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 identify compatible result sets, choose UNION or UNION ALL, align types and aliases, parameterize every branch, and read the consolidated result 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.