Sub-Query in Query Column Expressions

Introduction

Queries are the primary data processing tools in database systems. They work behind the scenes, shaping raw data into meaningful outputs such as reports and summaries. Many Microsoft Access users, especially beginners, try to build a report’s output data by chaining multiple tables/queries together in a single query, expecting the final result in just one or two steps. This approach often leads to difficulties in producing the correct output.

A better method is to start by planning the report—define its layout, required contents, grouping, and summarizing needs. If multiple related tables are involved, break down the process into smaller, manageable steps. Begin by joining a few tables or queries together in one query, then use that query as input for the next step. You can also create intermediate tables and build further queries on top of them to refine the data. In this process, make use of action queries such as Make-Table, Append, or Update to prepare and organize the data effectively.

When the Report Requirement is Complicated.

When a report’s contents are complex and cannot be built in a single step, my preferred approach is to create a Report Table. Data is brought into this table piece by piece from the source tables using queries or VBA routines, and then added or updated as needed before opening the report. Once the Report Table is populated, the report design becomes straightforward, since it is bound to a single, well-structured dataset.

These preparation steps can be fully automated with Macros or VBA.

To make the process flexible, necessary report parameters—such as date ranges or filter criteria—are stored in a Parameter Table. A Parameter Form is provided to capture or update these values. From this form, users can either:

• Re-run the procedure to refresh the Report Table with new parameter values, or

• directly open the report in Preview/Print mode if the data has already been prepared.

Using a Sub-Query in the Criteria Row.

Here, we will explore how to use Subqueries within Queries to filter records or incorporate data from other tables or queries.

Let us look at a simple Query that uses a sub-query in the criteria section to filter data from the Orders Table.   In the Orders table, about 830 Orders OrderIDs range from 10248 to 11077.  We need to filter certain Groups of Order (say Order Numbers 10248, 10254, 10260, 10263, 10267, 10272, 10283) for review.

Following is an SQL of a sample Query that filters the above Orders without the use of a Sub-Query:

SELECT Orders.*
FROM Orders
WHERE (((Orders.OrderID) In (10248,10254,10260,10263,10267,10272,10283)));

The above query works, but it has a limitation: whenever we want to filter a different set of Orders, we must manually modify the Criteria line by replacing the existing Order Numbers with the new ones. Clearly, we cannot expect end users to perform this task themselves.

A better approach is to provide users with a simple option to enter the required Order Numbers into a dedicated table (let’s call it ParamTable, with a single field: OrderNumber). This way, the query can automatically detect any changes to the table values at runtime. Users can type the desired Order Numbers directly into a Datasheet Form and then click a Command Button to run the query with the updated values.

To achieve this, we need to use a subquery in the criteria row of the main query. The subquery compares the OrderNumber values in the ParamTable with those in the Orders table, ensuring that only matching records are returned.

We will modify the Query to insert a Sub-Query in the Criteria Row to pull the values from the ParamTable and to use the OrderNumber field values as criteria. 

The modified SQL String of the Query is given below:

SELECT Orders.*
FROM Orders
WHERE (((Orders.OrderID) In (SELECT OrderNumber FROM OrderParam)));

The Sub-Query string in the Criteria Row is in Bold in the SQL above.

Sub_Query in a Query Column.

You may have already encountered the type of subquery shown above, but now we will explore a different use case: employing a subquery as an expression in a query column. This allows us to bring in values from another table that is related to the source table of the query. Although this technique is not commonly used, it can be extremely valuable in complex scenarios where conventional joins or query structures fall short.

When several tables are used in a Query with LEFT JOIN or RIGHT JOIN relationships, it becomes difficult to link all the related tables this way to incorporate summary values of one table. This is more so when one-to-many relationships are involved.

We will use Orders and Order Details Tables from the Northwind.mdb sample database for our example. Import both these tables from the Northwind.mdb sample database from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb.

The sample Query (in normal style), given below, uses both Tables in the Query, linked to the OrderID Field of both tables to create an Order-wise Summary from the Order Details Table.

SELECT Orders.OrderID,
 Orders.CustomerID,
 Sum((1-[Discount])*[UnitPrice]*[Quantity]) AS OrderVal
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY Orders.OrderID, Orders.CustomerID;

The same result can be achieved without placing the Order Details Table in the Query design. We will write an expression in a separate Column using a subquery to pull the summary Order-wise Total Value directly from the Order Details Table. Here is the example SQL String:

SELECT Orders.*,
    (SELECT  Sum((1-[Discount])*[UnitPrice]*[Quantity]) AS OrderValue
     FROM [Order Details] AS ODetails WHERE ([ODetails].[OrderID] = [Orders].[OrderID])
GROUP BY [ODetails].OrderID) AS OrderVal, [OrderVal]*0.12 AS Tax
FROM Orders;

In the Orders table, each order can have multiple related records in the Order Details table. By using a subquery (a totals query), we can calculate the total sales value for each order. The result of this calculation is then displayed in the corresponding row of the output. In other words, the subquery functions as an independent expression in its own column, running separately for each row in the Orders table.

The new column name: OrderValue, created, can be part of other expressions, and we have calculated the Tax value, 12% of Order Value, in a separate column.

Let us take a closer look at the Sub-Query.

1. The SELECT clause uses only one output column (Sum((1-[Discount])*[UnitPrice]*[Quantity]) AS OrderValue), and the expression is named OrderValue.  You should not use more than one column in the SELECT clause.

2. In the FROM clause, the Order Details Table is given a new name (ODetails), and this name is used to qualify the OrderID field in the WHERE clause.  The OrderID field appears in both Orders and Order Details Tables.

3. The WHERE clause in the Subquery is necessary to match the OrderIDs of both tables and place the calculated values in their matching row of Order Records.

Earlier Post Link References:

• Change Secure DB to Unsecured
• Menus with Options Group Control
• Date and Time Values
• Limit to List Combo Box
• Input Masks and Data Entry