Sub-Query in Query Column Expressions.
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 to create a new 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, there are about 830 Orders, and the OrderIDs range from 10248 to 11077. We need to filter certain Groups of Orders (say Order Numbers 10248, 10254, 10260, 10263, 10267, 10272, 10283) for review.
The following is the sample SQL 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 that 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 in the Criteria Row to insert a subquery to get the values from the ParamTable and 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.
The Sub_Query in a Query Column.
You may have already encountered the type of subquery shown above, but now we will explore a different approach: employing a subquery as an expression in a query column. This allows us to bring in values from another table related to the Query source table. 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 the 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) 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.
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.
In the FROM clause, the Order Details Table is given a new name (ODetails), and the name is used to qualify the OrderID field in the WHERE clause. The OrderID field appears in both the Orders and Order Details Tables.
The WHERE clause in the Subquery is necessary to match the OrderIDs of the tables and place the calculated value of the Order row matched Records.
Earlier Post Link References:
