Microsoft Access 2010 Bible

Part II: Programming Microsoft Access

476

Using these four basic keywords, you can build very powerful SQL statements to use in your

Access forms and reports.

Note
By convention, SQL keywords are entered in all uppercase. This is not a requirement, however, because Jet,
the database engine built into Microsoft Access, is case-insensitive.

Also, SQL statements may span many, many lines. The Jet database engine doesn’t care how long a SQL state-
ment is, or whether it spans multiple lines, as long as the SQL syntax (spaces, commas, and so on) is correct.

SELECT

The SELECT keyword is the first keyword used in two query types: in a Select query or a Make-

Table query. SELECT specifies the field(s) you want displayed in the result data.

After specifying the keyword SELECT, specify the fields you want included and displayed by the

query. The general syntax is:

SELECT Field_one, Field_two, Field_three [,...]

where Field_one, Field_two, and so on, are replaced with the names of the table fields. As many as

255 fields may be included in the SELECT statement.

Notice that commas separate each field in the list from the others. For example, use the following

SELECT clause to specify Company Name and City fields in the Customers table:

SELECT [Company Name], City

The last field name in the list is not followed by a comma.

Note
The field name Company Name needs square brackets around it because it has a space in the name (see the
“Using brackets around field names” sidebar for more information).

If you need to view fields from more than one table, specify the name of the tables in which to find

the fields. The SELECT statement would, for example, look like this to select fields from both the

Customers and Sales tables:

SELECT tblCustomers.Company, tblCustomers.City, tblSales.SaleDate,

tblSales.InvoiceNumber

The dot between tblCustomers and Company is an operator that indicates that Company is

contained within tblCustomers. In this context, the dot separates a table from a field name.

This is an example of how context determines how Access interprets operators.