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.

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.

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.

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.