Microsoft Access 2010 Bible

Chapter 13: Accessing Data with VBA Code ...............................................................................

475

As you add tables and choose fields in the Access Query Designer, behind the scenes, Access com-

poses a SQL statement that is stored within the database file. Anything you can do in the Query

Designer can be expressed using the Access SQL syntax. Opening a query actually instructs Access

to retrieve the SQL statement and compose the representation of the SQL statement you see in the

Query Designer.

You can make changes to the query using either the Design window or the SQL window. As you

work with the query, you can alternate between view modes using the View drop-down menu in

the Access ribbon. The changes you make in either view are immediately reflected in the alterna-

tive view. On occasion, however, changes in the SQL view cannot be viewed directly in the Design

window because the Design window cannot display a representation of every possible SQL state-

ment. These queries still run as expected but can only be designed in the SQL window.

Tip
If you’re proficient in creating SQL queries on your own, you can even create a new query directly in the SQL
window. To add new lines to the SQL statement, simply press Enter, or add additional SQL text to the existing
statement, while working in the SQL view window.

A SQL primer

One way to learn SQL syntax is to build a query in Design view; then view the corresponding SQL

statement in the SQL view window. The example in Figure 13.1 uses the four most common SQL

commands. Table 13.1 shows each command and explains its purpose. Each of these commands is

discussed in detail in the following sections.

TABLE 13.1

Four Common SQL Keywords

Keyword Purpose in SQL Statement

SELECT This keyword starts a SQL statement. It’s followed by the names of the fields that are selected

from the table or tables (if more than one is specified in the FROM clause). SELECT is a

required keyword for queries that extract data from tables.

FROM FROM is followed by the name(s) of the table(s) containing the fields specified in the SELECT

command. FROM is a required keyword for SELECT queries. If more than one table is used,

you must also specify a JOIN type so that Access knows how the data in the tables are related.

WHERE The WHERE keyword specifies conditions used to filter (limit) the records that are returned by

the SELECT. The WHERE keyword is optional and is used when you want to select only spe-

cific records from the underlying data source.

ORDER BY ORDER BY specifies the order in which you want the selected records to be sorted. The ORDER

BY clause is optional and used when you want records returned in a specific sequence.

Without an ORDER BY clause, Access returns records in an unpredictable order determined by

the database engine (Jet).