Fascinating Query Tricks

Those who are new to Access and especially those new to writing SQL in specific, will be fascinated at how easy it can be to produce the results you need, with very simple SQL and some not so difficult VBA code.

I love writing VBA code but I am convinced that, many times what I am trying to accomplish can be done in a more efficient, graceful and elegant way using SQL queries.  Over the last few months, I have tried to develop my applications with this in mind.  The following article is a summary of problems I have been able to solve by using fascinating query tricks.  Here is an overview of what we will cover:

• How to pass a variable parameter to a query calculation
• How to add "<All Employees>" to a list of employee names
• Use UNION query to add a Totals row to a dataset output

These common development tasks pop up all the time and some of these solutions I have been using for years.  They are the kind of things that are good to have in your toolbox.  If you don't already have a solution for these issues, download the demo application  for this article and store it with your stock code.

How to pass a variable parameter to a query calculation

VBA Solution:
The first tab of my demo form shows three different ways you can perform a price increase calculation.  In this example, I want to add a percentage to the price of a book.  The default is 10%, but this parameter should be adjustable.  The following shows two ways to accomplish this: one uses a VBA function and the other is strictly a query solution.

The following SQL Statement shows how the price could be calculated using a hard-coded value.  Of course, this is not very flexible or easy to adjust, so we want to make it dynamic.

  SELECT title_id, price, price * 1.10 AS Adj_Price FROM titles;

One way to make this calculation dynamic is to substitute a variable for the price increase percentage, except Access queries are not able to read variables, not even publicly declared ones.  The only way to pass a variable to a query is by means of a function that returns the variable, like this:

  SELECT title_id, price, price * FetchPricePct() AS Adj_Price FROM titles;

In this particular demo, the method FetchPricePct() returns the value of a public variable.  In order for this to fit into your code paradigm, you will need to have a function that also sets this public variable to some value.  Alternatively, you could put a reference directly to a control on a form, like this ...

  SELECT title_id, price, price * Forms!frmMain!txtPct AS Adj_Price FROM titles;

However, this creates a problem if the form is not open, or if the text box is empty, or if it doesn't contain a number.  You could, of course, write inline code to handle those exceptions in the query, but it gets kind of crowded ...

  SELECT title_id, price, price * 
         IIF(IsNumeric(Nz(Forms!frmMain!txtPct,1.1)), 
                 Nz(Forms!frmMain!txtPct,1.1), 1.1)AS Adj_Price FROM titles;

I have done this, and it works, but it's not pretty.  The IIF() function first checks to see if the value is numeric, substituting our default (1.10) for NULL, since NULL is neither numeric nor non-numeric.  If this test passes, then it uses the text box value but if it fails (the text "one point one zero" is not equal to 1.10) our default is supplied.  This kind of logic is better placed in a function which is easy to modify and even easier to reuse.  That is why I would discourage you from doing what is shown in the code snippet above.

Also, I've been known to do tricky things in the function, such as read values stored in the registry or search through open forms until I found one that has the particular ID I require.  That way the query is not tied to a specific form.  If  frmEmployee isn't open, maybe the EmplNo I seek can be found on a different form, such as frmTimeSheet.  Your functions can become very reusable.

There is one problem with this paradigm:  Access queries called from ASP web pages cannot resolve the source of the function.  They appear undefined to the ADODB layer and the query fails.  That brought me to my query based solution for this problem.

Query Solution:
What I really needed, I thought, was a table with a field that contained the price increase value.  If I could join this table to my Titles table, I could replace the function with a simple field value.  However, on what column could I join them?  No column, the answer turns out to be.  You create a Cartesian Product of the two tables by referencing both but assigning no kind of join.  The SQL for this solution looks like this:

  SELECT title_id, price, price * Pct_Increase AS Adj_Price 
  FROM titles, PriceVariable; 

There is a caveat: the tblPriceVariable table may have only one row.  If, for example, it contained two rows, then two records would be generated for every row in the Titles table.  Notice the right-most query result pane in the Figure 1 below and you will see what I mean.  Another thing you may notice from the image is that the tables using the Cartesian Product do not expose an "Add Record" line and the Add Record button is grayed out.  Because of the nature of the join, this recordset is not updateable.  For my ASP application, that was no problem, but if you need an updateable recordset, the Cartesian Product idea will not work for you.

How to add an "ALL" item to a list

A very common requirement when creating a drop down list is to include an "--ALL--" option.  For Access and Visual Basic projects, this must be done with the query, while with ASP web applications one can append the ALL option in VB Script.  Since the topic is Fascinating Query Tricks, and since the query option is more universal, we will focus on that solution.

Figure 2, taken from the demo code, shows an Access form with two drop down boxes: one with a simple employee list and the other list with an "--All Employees--" option.  The corresponding SQL that populates the list is shown in the white label and the WHERE clause used to leverage the selected value is shown in yellow.  The report buttons demonstrate how to wire up a report to the text box value, each launching a different report that references the corresponding drop down box.

This particular solution presumes that you are using an employee ID number as the key field, not the actual name, so the "--All Employees--" option must also be given an employee ID.  This is where it gets a little counter-intuitive.  In most cases, an EmplNo will be a long integer value, but because of the way we want to use the selected value, our new option will be given the EmplNo of "*" (the wildcard character).  The SQL looks like this:

  SELECT EmplNo,
Employee FROM Employees
  UNION SELECT "*", "-- All Employees --" 
  FROM MsysObjects ORDER BY Employee;

Because this SQL is created in Microsoft Access, the SELECT statement for the All Employees option must include a FROM clause.  (SQL Server allows you to execute a SELECT without a FROM clause.)  In the past, I would have created a dummy table with one record, but invariably I would end up forgetting what tblDummy was for and delete it.  Now I simply reference one of the Access system tables that I know will always be present ... and hidden.  Even though the MsysObjects table has many records, only one result is returned, a row with an EmplNo of "*" and an Employee name of "--All Employees--".

How do we now use the selected value?  One could use the method mentioned above, creating a function that tested the value of cboEmployee02 combo box and substituting the appropriate SQL statement for our report's record source.  However, in this case there is an easier way.  My demo report uses the selection by referencing it directly from the query that serves as its record source.  The sample report displays Employee Sales for one employee ... or for All Employees.  I use the same report and the same query, but the WHERE clause uses a LIKE criteria evaluation instead of an EQUALS, like this:

  SELECT Employees.Employee, Sales.ord_date, Sales.qty, Titles.title,
         Titles.price, [Price]*[Qty] AS Ext, Employees.EmplNo 
  FROM (Employees INNER JOIN Sales ON Employees.EmplNo = Sales.EmplNo) 
        INNER JOIN Titles ON Sales.title_id = Titles.title_id 

  WHERE Employees.EmplNo Like ([Forms]![frmMainDemo]![cboEmployee02]) 

  ORDER BY Employees.Employee, Sales.ord_date;

So, the result here is that if a long integer EmplNo is passed, the WHERE clause evaluates to this:

	WHERE Employees.EmplNo LIKE 1234

But if "--All Employees--" was selected then all records are returned with this criteria:

	WHERE Employees.EmplNo LIKE *

It's that simple.  One SQL statement.  One reference to the combo box.  Of course, if the form is not open, this report is going to fail.  That is why I often create a function to return the value anyhow.  In VBA code, I can check to see if the form is open and if not, substitute a default like 0 (to return no records) or * (to return them all).  Then the WHERE clause would look something like this:

  WHERE Employees.EmplNo LIKE GetSelectedEmplNo()

Add a summary TOTALS row to your query output

The final trick applies when you want your query results to include a Totals Row.  Of course, you can do this in an Access report with ease, but what if you just want to generate a query and display it to your users in datasheet view?  You need to implement a couple of clever little query tricks.

Once again, the demo application includes an example of how one might accomplish this and Figure 3 shows how the SQL might be constructed.  The first trick is to clone the primary SQL statement and turn it into a summary query.  Here is the process I go through to get to this result:

The sample code in the demo example looks like this.

  SELECT " " & [stor_name] As Store, Sum(qty) AS Quantity, 
         Sum(price) AS UnitPrice, Sum([price]*[qty]) AS Extension 
  FROM (Stores INNER JOIN Sales ON Stores.stor_id = Sales.stor_id) 
        INNER JOIN Titles ON Sales.title_id = Titles.title_id 
  GROUP BY stor_name 

  UNION SELECT "All Stores" AS Store, Sum(qty) AS Quantity, 
               Sum(price) AS UnitPrice, Sum([price]*[qty]) AS Extension 
  FROM (Stores INNER JOIN Sales ON Stores.stor_id = Sales.stor_id) 
        INNER JOIN Titles ON Sales.title_id = Titles.title_id 
  GROUP BY "All Stores";

Hopefully, you noticed the concatenation of a single space to the store name in the first select statement.  This is the second trick and its purpose is to force the items in the dataset to sort in the way we want.  If you do not add a space here, "All Stores" will tend to show up at the top.  You could change the row label to "Total For All Stores" but you had better hope you do not have a store name starting with a letter greater than "T" for that to work.

Figure 3 shows the results of the demo query with and without a totals row.  Our results are sorted correctly, although if you look closely you will see that there is a space in front of all but the final row, and the results gives our users a quick and clean way to view detail and summary data in the same dataset.

The Fascination Never Ends

Maybe some readers will take issue with the title "Fascinating" when it comes to these simple query tricks, but those who are new to Access and especially those new to writing SQL in specific, will probably be fascinated at how easy it can be to produce the results you need, with very simple SQL and some not so difficult VBA code.  In a future article, I will be discussing how to extend your SQL skills into the realm of DDL, Data Definition Language, which is used to create tables, indexes and the like.

» See All Articles by Columnist Danny J. Lesandrini