Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Sub-Query in Query Column Expressions

Introduction

Queries are the main data processing component of any database systems.  Queries work behind the scene shaping the data into Reports and other form of outputs.  Microsoft Access Users (mostly beginners) attempt to create the output for a Report by chaining several Tables in Queries together and the report data is expected to form in one or two query steps.  This approach may not be the correct one because it will be difficult to get the proper output for the Report.

First, you must plan your Report by defining layout, contents, grouping, summary etc.  If several related tables are involved in organizing the required data then you may take smaller steps of joining few tables or queries together in a Query.  Use this Query as input and combine with other tables in the next step.  You may create intermediate tables and crate Queries out of them for further processing of information.  In these steps you may use Make-table, Append Table, Update etc. to process the data.


When Report Requirement is Complicated.

When the Report contents are complicated and difficult to create at one go then my approach is to create a Report table and bring the data piece by piece from the source Table(s) with Queries/VBA Routines and add or update them into the Report Table before opening the Report.  These processing steps can be automated through Macros/VBA.

Necessary report parameters like date-range or filtering criteria values will be collected in a parameter table to use them in data processing queries.  There will be options in the Parameter Form either to Re-run the Report creation procedure with changed parameter values or to open the existing Report in Preview/Print mode. 


Sub-Query as Criteria.

Here, we are trying to explore the usage of Sub-Queries in Queries to filter or incorporate data from tables or from other 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 ranging OrderIDs from 10248 to 11077.  We need to filter certain Group of Orders (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 does the job but the problem is when we want a different set of Orders to be filtered then the Query's Criteria line needs to be modified physically to replace with new set of Order Numbers to filter.  We can not expect the User to do this task manually, instead we must provide them with an option to key-in the Order Numbers into a table (ParamTable with a single field: OrderNumber) and use it as criteria.  This method will facilitate automatic detection of change of values in the table at run time.  The User can simply type the Order Numbers on a Datasheet Form and click a Command Button to run the query with the changed order numbers.  With this method we need a Sub-Query in the criteria row to compare the Order Numbers in the Paramtable with the Order Numbers in the Orders Table and filter data.

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.

It is likely that you already came across the above sub-query before, but we are going to learn how to use a Sub-Query as an expression in a Query Column to incorporate values from a different table, related to the Query Source Table. This usage is not so common and it is very useful at difficult times.

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 the Tables in the Query, linked on 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 Sub-Query to pull the summary Order-wise Total Value directly from 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;

There are several records in the Order Details Table for a single order in the Orders Table.  Through the Sub-Query (Total Query) we sum-up the Sale Value of each Order and places the result in its corresponding row in the output.  So, the Sub-Query runs in a separate Column as an expression independently for each row for the Orders table for the output.

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

Let us take a closer look the Sub-Query.

  1. The SELECT clause uses only one output column (Sum((1-[Discount])*[UnitPrice]*[Quantity]) AS OrderValue) and the expression is named as OrderValue.  You should not use more than one columns 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 Sub-Query is necessary to match the OrderIDs of both tables and place the calculated values in their matching row of Order Records.
Share:

1 comment:

  1. [...] AS [avg] FROM my_Table GROUP BY my_Table.theme, my_Table.subtopic; Ref. Link: Sub-Query in Query Column Expressions __________________ http://www.msaccesstips.com (Learn MS-Access Tips and Tricks) Learn Advanced [...]

    ReplyDelete

Comments subject to moderation before publishing.

MS-Access Tips on your Finger-Tip

  • Download Android App 'MSA Guru' Version of LEARN MS-ACCESS TIPS AND TRICKS from Google Play Store.

Want to Post Free Ads on the Web


Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

Search

Popular Posts

Blog Archive

Powered by Blogger.

Follow by Email

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Menus and Toolbars Objects Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Dictionary Object Graph Charts Query VBA msaccessQuery Calculation Combo Boxes Event List Boxes Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Key Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances Item Macros Menus RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference TreeView Control Utility Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers ImageList Control Import Labels List ListView Control Logo Macro Mail Merge Main Form Memo Monitoring Nodes Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code