Fascinating Query Tricks - Page 2

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