A select query selecting a select statement

Executing a SELECT Query on a SELECT Statement in MS Access

Learn how to perform a 'SELECT on SELECT' operation in MS Access, effectively querying the results of another query. This technique is crucial for complex data analysis and reporting.

In Microsoft Access, you often encounter situations where you need to perform further analysis or filtering on the results of an existing query. While many SQL databases support subqueries directly in the FROM clause, Access has specific nuances. This article will guide you through the process of creating a 'SELECT on SELECT' query, which essentially means querying the output of another query. This is a powerful technique for breaking down complex data manipulation tasks into manageable steps.

Understanding the 'SELECT on SELECT' Concept

A 'SELECT on SELECT' operation, often referred to as a derived table or subquery in the FROM clause in other SQL dialects, allows you to treat the result set of one SELECT statement as if it were a temporary table. You can then apply further SELECT, WHERE, JOIN, or GROUP BY clauses to this temporary result set. This is particularly useful for:

• Aggregating aggregated data: Calculating averages of sums, or counts of distinct values from a grouped result.
• Step-by-step filtering: Applying initial filters in one query, then more complex filters on the reduced dataset.
• Simplifying complex joins: Breaking down multi-table joins into smaller, more manageable queries.
• Creating reusable data views: Defining a base query that can be easily queried by others without needing to understand its underlying complexity.

flowchart TD
    A[Start] --> B{"Initial SELECT Query (Query1)"}
    B --> C["Result Set of Query1"]
    C --> D{"Second SELECT Query (Query2)"}
    D --> E["Final Result Set"]
    E --> F[End]
    B --"Acts as a table"--> D

Conceptual flow of a 'SELECT on SELECT' operation

Implementing 'SELECT on SELECT' in MS Access

Unlike some other SQL databases that allow you to directly embed a SELECT statement in the FROM clause (e.g., SELECT * FROM (SELECT ...)), MS Access requires you to save your initial SELECT statement as a named query. Once saved, this named query can then be treated like a table in subsequent SELECT statements. This approach makes your database more organized and your queries more readable and maintainable.

Step-by-Step Example

Let's walk through an example. Imagine you have a table named Orders with OrderID, CustomerID, OrderDate, and OrderTotal fields. You want to find the average OrderTotal for customers who placed orders after a specific date.

1. Step 1: Create the Initial Query

First, create a query that filters orders placed after a certain date. In Access, go to Create > Query Design. Add the Orders table. Drag CustomerID and OrderTotal to the design grid. In the Criteria row for OrderDate, enter > #2023-01-01#. Save this query as OrdersAfter2023.

2. Step 2: Write the SQL for the Initial Query

The SQL for OrdersAfter2023 would look like this:

3. Step 3: Create the Second Query

Now, create a new query (again, Create > Query Design). Instead of adding a table, go to the Queries tab in the 'Show Table' dialog and add your saved query OrdersAfter2023. Drag CustomerID and OrderTotal to the design grid. For OrderTotal, change the Total row to Avg (or Average depending on your Access version). You might also want to group by CustomerID to get the average per customer. Save this query as AvgOrderTotalAfter2023.

4. Step 4: Write the SQL for the Second Query

The SQL for AvgOrderTotalAfter2023 would then reference your first query:

SELECT
    CustomerID,
    OrderTotal
FROM
    Orders
WHERE
    OrderDate > #2023-01-01#;

SQL for the initial query, OrdersAfter2023.

SELECT
    Q.CustomerID,
    Avg(Q.OrderTotal) AS AverageOrderTotal
FROM
    OrdersAfter2023 AS Q
GROUP BY
    Q.CustomerID;

SQL for the second query, AvgOrderTotalAfter2023, querying the first saved query.