The Access Wizard Newsletter Tips Tricks and Traps for Access Users and Developers
June 2005

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.

In this issue
  • Tip of the Month
  • What Does the User See?
  • What's Really Going on Behind the Curtain
  • So Why Should You Care?
  • A note to the purists out there

  • 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.

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services

    Join our mailing list!

    Forward email

    This email was sent to jim@custom-software.biz, by jim@custom-software.biz
    Powered by

    Custom Software | - | Westford | MA | 01886