SQL

Let's say your friend is spending the summer in Tokyo. The mailing address he gives you has the text string chome, which in Japanese roughly means "division" or "section."

The Japanese post office will have no problem interpreting chome on an envelope you address to him. On the other hand, chome is not Japanese. It only approximates in English the pronunciation of the actual Japanese word, written as .

Something similar is at work when you add fields, sorts, criteria, and more on the design grid. In the past several pages, I've been telling you to "create a query in Design view." That's not strictly accurate. What I've really been saying is to "use the graphical interface of the design grid to enter characters and symbols that Access can convert into a statement in Structured Query Language (SQL), a database language that Access understands and uses to run a query."

Do You Need to Know SQL?

If you've opened the drop-down for the View button on the Query Design toolbar, you've likely noticed that one of your choices is SQL. You can view in SQL view any query you create in Design view. It's good to have a little knowledge of SQL to have some appreciation of the language Access understands.

Most of the basic queries you'll likely want to make in Access can be created on the design grid without any understanding of SQL. But some queries, such as a union query, must be written in SQL. More important, SQL is the language of relational databases; if you want to do more advanced work in the field, you'll want to have a knowledge of the area.

Even if you have no real need for SQL, I don't want you to be so terrified of the term that you can't deal with it on any level. As you saw with the equally daunting normalization in Chapter 3, "Understanding Relationships," at least some aspects of complex and intimidating database subjects are perfectly accessible to nonprofessionals.

On the other hand, you're probably aware that "a little knowledge is a dangerous thing." Read the following dip-your-toe-in-the-water discussion in that light as well. To gain a solid understanding of SQL, I recommend SQL Queries for Mere Mortals, by Michael Hernandez and John Viescas (Addison-Wesley, 2000).

In this section, you'll learn a few SQL keywords to create a simple query. You'll also use SQL to execute a UNION query, a query type that can be created only in SQL.

Quick SQL Start

The key clauses in an SQL statement are listed in Table 8.1.

Table 8.1. SQL Statements

Keywords	Description
SELECT	Required. Begins the SQL statement and names the fields that will be selected from tables.
FROM	Required. Names the tables that contain the fields in SELECT.
ORDER BY	Not required. Determines the sort.
WHERE	Not required. Specifies criteria.

The first key clause is SELECT. You use it to start the SQL statement and select the field(s) you want in your query.

The second key clause is FROM. You use it to tell Access which table(s) contain the fields you specified in the SELECT clause (the SELECT part of the statement).

Let's create a very simple query using these two keywords. You'll retrieve records for the first two fields of the Categories table:

1.	In the Database window, click Queries and click New. With Design View chosen in the New Query dialog box, click OK. Close the Show Table dialog box without adding any tables.
2.	Save the query as qrySQL_Practice.
3.	Click SQL on the View button. (If SQL is not displayed, click the drop-down arrow of the View button and select SQL View.) You see the word SELECT followed by a semicolon.
4.	Highlight the semicolon and delete it. The semicolon closes the statement and tells Access you're done. But Access is usually quite forgiving if you don't include it. So for the purposes of this exercise, I ask you to delete it for now.
5.	Click after SELECT. Press the spacebar once. Type CategoryID This tells Access to retrieve the CategoryID field.
6.	Type a comma after CategoryID and press the spacebar once. Type CategoryName. You use commas to separate the fields you want to retrieve. Your statement thus far is SELECT CategoryID, CategoryName.
7.	Press Enter. You could just press the spacebar, but I think it's clearer to put each clause on separate lines.
8.	Type FROM tblCategories
9.	Click View to run the query and have Access retrieve your records.

Apply a Sort with ORDER BY

You can sort the records by using the keyword ORDER BY. Let's sort by ascending CategoryName:

1.	Open the View drop-down list and select SQL View.
2.	Click after tblCategories and press Enter.
3.	Type ORDER BY CategoryName
4.	Click View to see your records.
5.	Click the View drop-down and select SQL View. Add a space after CategoryName and type DESC. For a descending sort, add DESC after the field.
6.	Click View to see your records. The records are now sorted by category name in descending order.

Add Criteria with WHERE

You use the WHERE clause to add criteria to your SQL statement. The WHERE clause must follow the FROM clause. Suppose you want to select just those categories that begin with the letters G through Z:

1.	Click the View drop-down button and select SQL View.
2.	Click after tblCategories. Press Enter.
3.	Type WHERE tblCategories.CategoryName>"g".

Here are the details:

• WHERE is the keyword.

• tblCategories.CategoryName is the identifier for the field. It includes the table name, followed by a period, followed by the field name.

• > is the greater-than comparison operator.

• g is the literal value, which you enclose in quotation marks. Figure 8.14 shows the SQL statement.

  Figure 8.14. The SQL statement in SQL view.

  

  
  

4.	Click View to see your records (see Figure 8.15). Figure 8.15. Access retrieves CategoryIDs and names for categories that begin with the letters g through z. Only categories beginning with letters after g are included.
5.	Save the query and close it.

NOTE

Don't be surprised or upset if Access modifies your SQL statement when you switch between Design and SQL views.

UNION Queries

Usually when you create queries, you want to include various fields from various tables, but you don't want to physically combine them. In a UNION query, you can combine fields with compatible data types (such as text with text, numbers with numbers) in a single column. The fields in the underlying tables remain as they are.

For example, imagine that to improve the logistics operations at your firm, you want to do a telephone survey of both suppliers and shippers. The researcher doing the survey asks you for a list of companies and their phone numbers that makes no distinction between suppliers and shippers.

You therefore need a list that combines the CompanyName of the Suppliers table with the CompanyName of the Shippers table, and the phone fields of the two tables as well. You can use a UNION query to obtain this information.

1.	In the Database window, click Queries and click New. With Design view chosen in the New Query dialog box, click OK. Close the Show Table dialog box without adding any tables.
2.	Choose View, SQL View. Delete the colon after SELECT.
3.	Press the spacebar once and type SuppCompanyName AS CompanyName. SuppCompanyName is the field that contains supplier company names in the Suppliers table. The AS keyword renames the field in the query.
4.	Type a comma, press the spacebar once, and type SuppPhone AS CompanyPhone. Similarly, you rename the phone field so it applies to both suppliers and shippers.
5.	Press Enter.
6.	Type FROM tblSuppliers. You use the FROM clause to designate the table.
7.	Press Enter twice. Type UNION SELECT. As you can see, the UNION query requires two SQL SELECT statements. Each has the same number of fields in the same order.
8.	Press the spacebar once. Type ShipCompanyName AS CompanyName, ShipPhone AS CompanyPhone. As with the supplier fields, you rename them so that the field names apply to all values.
9.	Press Enter. Type FROM tblShippers to specify the table where the fields reside.
10.	Press Enter twice. Type ORDER BY CompanyName;. This clause sorts the values. The semicolon ends the statement.
11.	Your window should look like Figure 8.16. Save the query as qryCompanyPhones. Figure 8.16. A UNION query can be designed only in SQL View.
12.	Click View to see your records. The query includes both shipper and supplier phone numbers, sorted by company name.

Q&A

Q1:

You knew this question was coming: How do I format the phone numbers so they're easier to read? While I'm at it, what about some clearer column headings?

A1:

Well, you know you can do those things in Design view. Because it's a UNION query, however, that view isn't available. But it's not a big problem. Here's what you can do: Choose File, Save As; open the bottom drop-down dialog box; and save the query as either a form (which, remember, has a Datasheet view) or a report. Use the Caption property on the CompanyName and CompanyPhone labels to set column headings. You can format the phone numbers by using the Format property (see Chapter 6, "Entering, Editing, and Displaying Data," for help). The solution database NiftyLionsChap8End.mdb includes this form as frmCompanyPhones.