Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

Understanding SQL

Access SQL is the language that underlies Access queries, so you need to understand a little bit about it, where it came from, and how it works. Access SQL enables you to construct queries without using the Access Query By Example (QBE) grid. This is necessary, for example, if you must build a SQL statement on the fly in response to user interaction with your application. Furthermore, certain operations supported by Access SQL aren't supported by the graphical QBE grid. You must build these SQL statements in the Query Builder's SQL view. In addition, there are many times when you will want to build the record source for a form or report on the fly. In those situations, you must have command of the SQL language. Finally, you will want to use SQL statements in your ADO and Data Access Objects (DAO) code. For all these reasons, learning SQL is a valuable skill.

What Is SQL, and Where Did It Come From?

SQL is a standard from which many different dialects have emerged. It was developed at an IBM research laboratory in the early 1970s and first formally described in a research paper released in 1974 at an Association for Computing Machinery meeting. Jet 4.0, the version of the Jet Engine provided with Access 2000 and above, has two modes: one supports Access SQL and the other supports SQL-92. The SQL-92 extensions are not available from the user interface. They can only be accessed using ADO. They are covered in a later section of this chapter, "Jet 4.0 ANSI-92 Extensions."

What Do You Need to Know About SQL?

At the very least, you need to understand SQL's basic constructs, which enable you to select, update, delete, and append data by using SQL commands and syntax. Access SQL is made up of very few verbs. The sections that follow cover the most commonly used verbs.

SQL Syntax

SQL is easy to learn. When retrieving data, you simply build a SELECT statement. SELECT statements are composed of clauses that determine the specifics of how the data is selected. When they're executed, SELECT statements select rows of data and return them as a recordset.

NOTE

In the examples that follow, keywords appear in uppercase. Values that you supply appear italicized. Optional parts of the statement appear in square brackets. Curly braces, combined with vertical bars, indicate a choice. Finally, ellipses are used to indicate a repeating sequence.

The SELECT Statement

The SELECT statement is at the heart of the SQL language. It is used to retrieve data from one or more tables. Its basic syntax is

SELECT column-list FROM table-list WHERE

where-clause ORDER BY

order-by-clause

The SELECT Clause

The SELECT clause specifies what columns you want to retrieve from the table whose data is being returned to the recordset. The basic syntax for a SELECT clause is

SELECT

column-list

The simplest SELECT clause looks like this:

SELECT

*

This SELECT clause retrieves all columns from a table. Here's another example that retrieves only the ClientID and CompanyName columns from a table:

SELECT

ClientID, CompanyName

Not only can you include columns that exist in your table, but you also can include expressions in a SELECT clause. Here's an example:

SELECT

ClientID, City & ", " & State & " " & PostalCode AS

Address

This SELECT clause retrieves the ClientID column as well as an alias called Address, which includes an expression that concatenates the City, State, and PostalCode columns.

The FROM Clause

The FROM clause specifies the tables or queries from which the records should be selected. It can include an alias you use to refer to the table. The FROM clause looks like this:

FROM

table-list [AS

alias ]

Here's an example of a basic FROM clause:

FROM

tblClients AS

Clients

In this case, the name of the table is tblClients, and the alias is Clients. If you combine the SELECT clause with the FROM clause, the SQL statement looks like this:

SELECT

ClientID, CompanyName FROM

tblClients

This SELECT statement retrieves the ClientID and CompanyName columns from the tblClients table.

Just as you can alias the fields included in a SELECT clause, you can also alias the tables included in the FROM clause. The alias is used to shorten the name, to simplify a cryptic name, and for a variety of other reasons. Here's an example:

SELECT ClientID, CompanyName FROM tblClients AS

Customers

This SQL statement selects the ClientID and CompanyName fields from the tblClients table, aliasing the tblClients table as Customers.

The WHERE Clause

The WHERE clause limits the records retrieved by the SELECT statement. You must follow several rules when building a WHERE clause. The text strings that you are searching for must be enclosed in quotes. Dates must be surrounded by pound (#) signs. Finally, you must include the keyword LIKE when using wildcard characters. A WHERE clause can include up to 40 columns combined by the keywords AND and OR. The syntax for a WHERE clause looks like this:

WHERE

expression1 [{AND|OR}

expression2 [...]]

A simple WHERE clause looks like this:

WHERE

Country = "USA"

Using an AND to further limit the criteria, the WHERE clause looks like this:

WHERE

Country = "USA" AND ContactTitle Like "Sales*"

This WHERE clause limits the records returned to those in which the country is equal to USA and the ContactTitle begins with Sales. Using an OR, the SELECT statement looks like this:

WHERE

Country = "USA" OR Country = "Canada"

This WHERE clause returns all records in which the country is equal to either USA or Canada. Compare that with the following example:

WHERE

Country = "USA" OR ContactTitle Like "Sales*"

This WHERE clause returns all records in which the Country is equal to USA or the ContactTitle begins with Sales. For example, if the ContactTitle for the salespeople in China begins with Sales, the names of those salespeople will be returned from this WHERE clause. The WHERE clause combined with the SELECT and FROM clauses looks like this:

SELECT

ClientID, CompanyName FROM

tblClients
WHERE

Country = "USA" OR Country = "Canada"

NOTE

Although Access SQL uses quotes to surround text values you're searching for, the ANSI-92 standard dictates that apostrophes (single quotes) must be used to delimit text values).

The ORDER BY Clause

The ORDER BY clause determines the order in which the returned rows are sorted. It's an optional clause, and it looks like this:

ORDER BY

column1 [{ASC|DESC}],

column2 [{ASC|DESC}] [,...]]

Here's an example:

ORDER BY

ClientID

The ORDER BY clause can include more than one field:

ORDER BY

Country, ClientID

When more than one field is specified, the left-most field is used as the primary level of sort. Any additional fields are the lower sort levels. Combined with the rest of the SELECT statement, the ORDER BY clause looks like this:

SELECT

ClientID, CompanyName FROM

tblClients
WHERE

Country = "USA" OR Country = "Canada"
ORDER BY

ClientID

The ORDER BY clause allows you to determine whether the sorted output appears in ascending or descending order. By default, output appears in ascending order. To switch to descending order, use the optional keyword DESC. Here's an example:

SELECT

ClientID, CompanyName FROM

tblClients ORDER BY

ClientID DESC

This example selects the ClientID and CompanyName fields from the tblClients table, ordering the output in descending order by the ClientID field.

The JOIN Clause

Often you'll need to build SELECT statements that retrieve data from more than one table. When building a SELECT statement based on more than one table, you must join the tables with a JOIN clause. The JOIN clause differs depending on whether you join the tables with an INNER JOIN, a LEFT OUTER JOIN, or a RIGHT OUTER JOIN.

The SQL-89 and SQL-92 syntax for joins differs. The basic SQL-89 syntax is

SELECT

column-list FROM

table1, table2 WHERE

table1.column1 = table2.column2

The SQL-92 syntax is preferred because it separates the join from the WHERE clause. It is

SELECT

column-list FROM

table1 {INNER|LEFT [OUTER]|RIGHT [OUTER]} JOIN

table2
ON

table1.column1 = table2.column2

Note that the keyword OUTER is optional.

Here's an example of a simple INNER JOIN:

SELECT DISTINCTROW

tblClients.ClientID,

tblClients.CompanyName, tblProjects.ProjectName,

tblProjects.ProjectDescription
FROM

tblClients
INNER JOIN

tblProjects ON

tblClients.ClientID = tblProjects.ClientID

Notice that four columns are returned in the query result. Two columns are from tblClients and two are from tblProjects. The SELECT statement uses an INNER JOIN from tblClients to tblProjects based on the ClientID field. This means that only clients who have projects are displayed in the query result. Compare this with the following SELECT statement:

SELECT DISTINCTROW

tblClients.ClientID,

tblClients.CompanyName, tblProjects.ProjectName,

tblProjects.ProjectDescription
FROM tblClients
LEFT JOIN

tblProjects ON

tblClients.ClientID = tblProjects.ClientID

This SELECT statement joins the two tables using a LEFT JOIN from tblClients to tblProjects based on the ClientID field. All clients are included in the resulting records, whether or not they have projects.

NOTE

The word OUTER is assumed in the LEFT JOIN clause used when building a left outer join.

There are times when you will need to join more than two tables in a SQL statement. The ANSI-92 syntax is

FROM

table1 JOIN

table2 ON

condition1 JOIN

table3 ON

condition2

The following example joins the tblClients, tblProjects, and tblPayments tables:

SELECT tblClients.ClientID, tblClients.CompanyName,
tblProjects.ProjectName, tblPayments.PaymentAmount
FROM (tblClients
INNER JOIN tblProjects
ON tblClients.ClientID = tblProjects.ClientID)
INNER JOIN tblPayments
ON tblProjects.ProjectID = tblPayments.ProjectID

In the example, the order of the joins is unimportant. The exception to this is when inner and outer joins are combined. When combining inner and outer joins, the Jet Engine applies two specific rules. First, the nonpreserved table in an outer join cannot participate in an inner join. The nonpreserved table is the one whose rows might not appear. In the case of a left outer join from tblClients to tblProjects, the tblProjects table is considered the nonpreserved table. It therefore cannot participate in an inner join with tblPayments. The second rule is that the nonpreserved table in an outer join cannot participate with another nonpreserved table in another outer join.

Self-Joins

Self-joins were covered earlier in the chapter. The SQL syntax required to create them is similar to a standard join and is covered here:

SELECT [FirstName] & " " & [LastName] AS EmployeeName,
[FirstName] & " " & [LastName] AS SupervisorName
FROM tblEmployees
INNER JOIN tblEmployees
AS tblSupervisors
ON tblEmployees.SupervisorID = tblSupervisors.EmployeeID

Notice that the tblEmployees table is joined to an alias of the tblEmployees table that is referred to as tblSupervisors. The SupervisorID from the tblEmployees table is joined with the EmployeeID field from the tblSupervisors alias. The fields included in the output are the FirstName and LastName from the tblEmployees table, and the FirstName and LastName from the alias of the tblEmployees table.

Non-Equi Joins

So far, all the joins that we have covered involve situations where the value of a field in one table is equal to the value of the field in the other table. You can create non-equi joins where the >, >=, <, <=, <>, or Between operator is used to join two tables. Here's an example:

SELECT tblClients.CompanyName, tblProjects.ProjectName
FROM tblClients
INNER JOIN tblProjects
ON tblClients.ClientID = tblProjects.ClientID
AND tblProjects.ProjectBeginDate >= tblClients.IntroDate

This example returns only the rows from tblProjects where the ProjectBeginDate is on or after the IntroDate stored in the tblClients table.

ALL, DISTINCTROW, and DISTINCT Clauses

The ALL clause of a SELECT statement means that all rows meeting the WHERE clause are included in the query result. When the DISTINCT keyword is used, Access eliminates duplicate rows, based on the fields included in the query result. This is the same as setting the Unique Values property to Yes in the graphical QBE grid. When the DISTINCTROW keyword is used, Access eliminates any duplicate rows based on all columns of all tables included in the query (whether they appear in the query result or not). This is the same as setting the Unique Records property to Yes in the graphical QBE grid. These keywords in the SELECT clause look like this:

SELECT [{ALL|DISTINCT|DISTINCT ROW}]

column-list

The TOP Predicate

The Top Values property, available via the user interface, is covered in the "Special Query Properties" section of this chapter. The keyword TOP is used to implement this feature in SQL. The syntax looks like this:

SELECT [{ALL|DISTINCT|DISTINCTROW}] [TOP

n [PERCENT]]

column-list

The example that follows extracts the five clients whose IntroDate field is most recent:

SELECT TOP 5 tblClients.ClientID, tblClients.CompanyName, tblClients.IntroDate
FROM tblClients
ORDER BY tblClients.IntroDate DESC

The GROUP BY Clause

The GROUP BY clause is used to calculate summary statistics; it's created when you build a Totals query by using the graphical QBE grid. The syntax of the GROUP BY clause is

GROUP BY

group-by-expression1 [

,group-by-expression2 [,...]]

The GROUP BY clause is used to dictate the fields on which the query result is grouped. When multiple fields are included in a GROUP BY clause, they are grouped from left to right. The output is automatically ordered by the fields designated in the GROUP BY clause. In the following example, the SELECT statement returns the country, city, and total freight for each country/city combination. The results are displayed in order by country and city:

SELECT DISTINCTROW tblCustomers.Country, tblCustomers.City,
Sum(tblOrders.Freight) AS SumOfFreight
FROM tblCustomers
INNER JOIN tblOrders ON tblCustomers.CustomerID = tblOrders.CustomerID
GROUP BY tblCustomers.Country, tblCustomers.City

The GROUP BY clause indicates that detail for the selected records isn't displayed. Instead, the fields indicated in the GROUP BY clause are displayed uniquely. One of the fields in the SELECT statement must include an aggregate function. This result of the aggregate function is displayed along with the fields specified in the GROUP BY clause.

The HAVING Clause

A HAVING clause is similar to a WHERE clause, but it differs in one major respect: It's applied after the data is summarized rather than before. In other words, the WHERE clause is used to determine which rows are grouped. The HAVING clause determines which groups are included in the output. A HAVING clause looks like this:

HAVING

expression1 [{AND|OR}

expression2[...]]

In the following example, the criterion > 1000 will be applied after the aggregate function SUM is applied to the grouping:

SELECT DISTINCTROW tblCustomers.Country, tblCustomers.City,
Sum(tblOrders.Freight) AS SumOfFreight
FROM tblCustomers
INNER JOIN tblOrders ON tblCustomers.CustomerID = tblOrders.CustomerID
GROUP BY tblCustomers.Country, tblCustomers.City
HAVING (((Sum(tblOrders.Freight))>1000))

Applying What You Have Learned

You can practice entering and working with SQL statements in two places:

• In a query's SQL View window

• In VBA code

Let's take a look at both of these techniques.

Using the Graphical QBE Grid as a Two-Way Tool

A great place to practice writing SQL statements is in the SQL View window of a query. It works like this:

Including SQL Statements in VBA Code

You can also execute SQL statements directly from VBA code. You can run a SQL statement from VBA code in two ways:

• You can build a temporary query and execute it.

• You can open a recordset with the SQL statement as the foundation for the recordset.

The VBA language enables you to build a query on the fly, execute it, and never store it. The code looks like this:

Sub CreateTempQuery()
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = "Select ProjectID, ProjectName from " & _
"tblProjects Where ProjectTotalEstimate > 30000"
.CommandType = adCmdText
.Prepared = True
Set rst = .Execute
End With
Do Until rst.EOF
Debug.Print rst!ProjectID, rst!ProjectName
rst.MoveNext
Loop
End Sub

Working with recordsets is covered extensively in Chapter 14. For now, you need to understand that this code creates a temporary query definition using a SQL statement. In this example, the query definition is never added to the database. Instead, the SQL statement is executed but never stored.

A SQL statement can also be provided as part of the recordset's Open method. The code looks like this:

Sub OpenRWithSQL()
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "Select ProjectId, ProjectName from " & _
"tblProjects Where ProjectTotalEstimate > 30000", _
CurrentProject.Connection
Do Until rst.EOF
Debug.Print rst!ProjectID, rst!ProjectName
rst.MoveNext
Loop
End Sub

Again, this code is discussed more thoroughly in Chapter 14. Notice that the Open method of the recordset object receives two parameters: The first is a SELECT statement, and the second is the Connection object.