What's Really Happening with Queries -- Pay No Attention to the Man Behind the Curtain
Most of you are probably familiar with the classic
film "The Wizard of Oz". It's a wonderful story of
overcoming obstacles, discovering what's really
important, and in some instances, learning that what
seems to be going on is just smoke and mirrors.
In one of my favorite scenes, Dorothy, The Tin Man,
The Cowardly Lion and The Scarecrow have returned
from their task of getting the broom from the Wicked
Witch of the West (no mean feat that!) The Wizard
had promised that upon completion of their mission
he would grant their various wishes. He was no doubt
surprised when they were able to do the unexpected.
Of course, his ability to grant their wishes was not all
it was cracked up to be. When they appeared before
him, he tried avoiding them by telling them to come
back the next day.
This really ticked Dorothy off, and she began yelling
at the Wizard to keep his promise. This caused great
noise and flames to come from the image of the
Wizard projected before our cowering heroes. As the
noise built and the flames thickened, the little dog,
Toto ("And your little dog, too!") pulled back the
curtain concealing the wizard, to reveal a mere man
behind various contraptions. In a last ditch effort to
maintain his illusion of mystique, he boomed into the
microphone "PAY NO ATTENTION TO THE MAN
BEHIND THE CURTAIN!" However, it was all too
obvious that the Wizard was far from what he
originally appeared to be.
That leads us to this month's topic - What's really
happening when you execute a query. Like the
difference between the all-powerful Wizard of Oz and
the "man behind the curtain", queries have
something very different going on behind the scenes.
This month we'll pull back the curtain on the typical
query to discover the secret.
What Does the User See? |
 |
Let's examine a typical query to see what's going on.
For our exercise we'll revisit a query from a summer
camp database that we used several months back.
In this window (the Query by Example, or QBE
window), we're setting up a query to show all
campers in the Cherokee cabin, sorted by last name
in alphabetical order.
When this query is run, results (in the datasheet
view) show:
>
Simple, right? You fill in the fields and check a few
boxes to build the query, and out come the results.
Alas, things are not always as simple as they
appear! What you may not realize is that behind the
scenes, Access is running a Structured Query
Language (SQL) statement. Yes, there is more going
on in a query than meets the eye.
|
What's Really Going on Behind the Curtain |
 |
To see what's going on behind the scenes, open up
any query in the design or datasheet view and select
SQL View from the View menu at the top of the
screen. When you do, you'll see a screen filled with
text similar to what we get with our sample query
above:
SELECT tblCabin.CabinName, [FName] & " " &
[Lname] AS Camper, tblCamper.DOB
FROM tblCabin INNER JOIN tblCamper ON
tblCabin.CabinKey = tblCamper.CabinKey
WHERE (((tblCabin.CabinName)="Cherokee"))
ORDER BY tblCamper.Lname;
This is an example of a typical SQL statement. If we
dissect this statement, we see that there are four
major clauses to consider:
The Select clause
SELECT tblCabin.CabinName, [FName] & " " &
[Lname] AS Camper, tblCamper.DOB
The Select clause of the SQL statement details
which fields or expressions the user will see in the
query results. The SQL statement gets a bit
verbose, designating the fields in the format "table
name.field name" (e.g. tblCabin.CabinName). If the
query you built requires concatenation, you will see
that included in this statement as well. If you've
used a column label instead of a field name, you'll see
the word AS following your column name(s).
The From Clause
FROM tblCabin INNER JOIN tblCamper ON
tblCabin.CabinKey = tblCamper.CabinKey
The FROM clause of the SQL statement indicates the
table(s) the data is coming from as well as any
linking fields to join the tables. It also indicates any
queries that might be acting as a source for this
query as well.
In our case, this clause joins the Camper and Cabin
tables on the Cabin Key. If you've simply linked two
tables with no fancy modifications, you'll see the
word INNER JOIN. This indicates that the two tables
are connected only where the two fields are equal.
There are other types of joins: outer joins, left joins,
right joins, self-joins, Cartesian joins, etc. They each
have their purpose and I'll be covering those in future
articles.
The Where Clause
WHERE (((tblCabin.CabinName)="Cherokee"))
This section designates "criteria" - a condition that
defines what data is selected for, or filtered out
from,
the results. The "degree of difficulty" for this
statement can range from really simple to stunningly
complex. This example happens to be
straightforward: CabinName = "Cherokee". The
quotes around the cabin name are required because
Cherokee is a string of characters, not a number,
date, or expression.
If there is no criteria (e.g., you want to return
everything) then the Where clause will not appear in
the SQL statement.
The Order By Clause
ORDER BY tblCamper.Lname;
This is the SQL clause that tells Access how to sort
the records that are returned in the query. In our
case, we want them sorted alphabetically by Last
Name (Lname is the field that holds this information).
This is not limited to a single field -- you may sort by
multiple fields. For instance, if you had two brothers
in the cabin with identical last names, you may want
them sorted initially by last name and then by first
name. The clause to do this is:
ORDER BY tblCamper.Lname, tblCamper.FName;
You may note that when we looked at the results of
the query in the datasheet view, the Last Name field
did not appear as a separate column. Instead, the
results appear, sorted by last name, in a column with
the first name. If you take a look at the select
statement above, you'll see that the LName field is
part of our concatenated Camper Name, but doesn't
appear as a separate column. We accomplished this
by unchecking the box in the Show row of the design
view.
|
So Why Should You Care? |
 |
You might be saying to yourself at this stage of the
game (or maybe long before this) -- Why should I
care about any of this SQL stuff when I can do all
my work in the design view and never even look at
the SQL behind the scenes?
There are some very important reasons why you
should care:
There are some things you can do only with
SQL - see this month's tip for a preview.
Understanding gives you more power. As
with the process of learning arithmetic, you could
make the argument that, in the age of cheap
calculators, there's really no need to learn addition,
subtraction, multiplication or division. The
understanding of what's really happening, however,
leads to innovation and the realization that there's
more than one path to a solution. When a query is
not working correctly, I'll frequently look at the SQL
code for clues as to what's going wrong.
Portability. SQL is a very popular database
query language because it is open source code. SQL
also comes in many varieties (MySQL, PostgreSQL,
Sybase, Oracle, just to name a few). If you ever find
yourself in a situation where you must create a
database using other software platforms, having
learned the SQL statements behind the queries will
help speed your learning of the SQL on the new
platform. One word of warning - although the SQL
languages are similar, there're not identical, so you'll
have to make adjustments along the way. It's like
hearing English speakers from New England, the
Southern U.S. and Australia -- they're all speaking
the same language, with obvious differences.
Copying SQL statements. If you ever need
to give someone a copy of a query you've created,
the easiest way is to go into the SQL window, copy
the text, email it, and have your recipient paste it
back into a SQL window. This is considerably more
efficient then the alternative of emailing a database
along with the query.
|
A note to the purists out there |
 |
All non-purists and non-techies can safely skip this
section and do something useful with your time.
To the purists: I recognize that some schools of
thought consider the From Clause and the Where
Clause as part of the Select Clause, and for good
reason. For the purpose of helping people understand
how things work, I'm a member of the school that
considers them separate, and I really believe that
breaking them down into component pieces helps
speed comprehension.
|
|
Tip of the Month |
|
It is wise to learn the ins and outs of SQL. There are
many online sites and books to help with this - just
Google the term "SQL tutorial" and find one that
speaks to you.
The more you know about the language the more
skills you have to create powerful queries. Did you
know that there are some queries that you cannot
create in the Query By Design window (the window
you see when you choose Query - Design view)? The
visual interface as it exists simply doesn't support it.
The most common example of this is something called
a Union Query--a specialized and very helpful query
that I'll be covering in next month's issue.
Bonus Tip:
You can make a change in the SQL window and it will
carry back to the QBE window. Try making changes
in either window and then look at how it affects the
other view.
|
|