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:
1) Create your primary results query
2) Copy the SQL into a new query window and convert it into the Summary
results you require, keeping all of the same columns, substituting static text
where necessary.
3) Add both of these SQL statements to a new query, joining them
with the UNION keyword.
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