Introduction to Access SQL

When you want to retrieve data from a database, you ask for the data by using Structured Query Language, or SQL. SQL is a computer language that closely resembles English that database programs understand. Knowing SQL is important because every query in Microsoft Access uses SQL. Understanding how SQL works can help create better queries, and can make it easier for you to fix a query when it is not returning the results that you want.

 Note   You cannot edit SQL for a web query.

In this article


What is SQL?

SQL is a computer language for working with sets of facts and the relationships between them. Relational database programs, such as Access, use SQL to work with data. Like many computer languages, SQL is an international standard that is recognized by standards bodies such as ISO and ANSI.

You use SQL to describe sets of data that can help you answer questions. When you use SQL, you must use the correct syntax. Syntax is the set of rules by which the elements of a language are correctly combined. SQL syntax is based on English syntax, and uses many of the same elements as Visual Basic for Applications (VBA) (Visual Basic for Applications (VBA): A macro-language version of Microsoft Visual Basic that is used to program Microsoft Windows-based applications and is included with several Microsoft programs.) syntax.

For example, a simple SQL statement that retrieves a list of last names for contacts whose first name is Mary might resemble this:

SELECT Last_Name
FROM Contacts
WHERE First_Name = 'Mary';

 Note   SQL is not only used for manipulating data, but also for creating and altering the design of database objects, such as tables. The part of SQL that is used for creating and altering database objects is called data-definition language (DDL). This topic does not cover DDL. For more information, see the article Create or modify tables or indexes by using a data-definition query.

SELECT statements

To describe a set of data by using SQL, you write a SELECT statement. A SELECT statement contains a complete description of a set of data that you want to obtain from a database. This includes the following:

  • What tables contain the data.
  • How data from different sources is related.
  • Which fields or calculations will produce the data.
  • Criteria that data must match to be included.
  • Whether and how to sort the results.

SQL clauses

Like a sentence, a SQL statement has clauses. Each clause performs a function for the SQL statement. Some clauses are required in a SELECT statement. The following table lists the most common SQL clauses.

SQL clause What it does Required
SELECT Lists the fields that contain data of interest. Yes
FROM Lists the tables that contain the fields listed in the SELECT clause. Yes
WHERE Specifies field criteria that must be met by each record to be included in the results. No
ORDER BY Specifies how to sort the results. No
GROUP BY In a SQL statement that contains aggregate functions, lists fields that are not summarized in the SELECT clause. Only if there are such fields
HAVING In a SQL statement that contains aggregate functions, specifies conditions that apply to fields that are summarized in the SELECT statement. No

SQL terms

Each SQL clause is composed of terms — comparable to parts of speech. The following table lists types of SQL terms.

SQL term Comparable part of speech Definition Example
identifier noun A name that you use to identify a database object, such as the name of a field. Customers.[Phone Number]
operator verb or adverb A keyword that represents an action or modifies an action. AS
constant noun A value that does not change, such as a number or NULL. 42
expression adjective A combination of identifiers, operators, constants, and functions that evaluates to a single value. >= Products.[Unit Price]

Top of Page Top of Page

Basic SQL clauses: SELECT, FROM, and WHERE

A SQL statement takes the general form:

SELECT field_1
FROM table_1
WHERE criterion_1
;

 Notes 

  • Access ignores line breaks in a SQL statement. However, consider using a line for each clause to help improve the readability of your SQL statements for yourself and others.
  • Every SELECT statement ends with a semi-colon (;). The semi-colon can appear at the end of the last clause or on a line by itself at the end of the SQL statement.

An example in Access

The following illustrates what a SQL statement for a simple select query might look like in Access:

SQL object tab showing a SELECT statement

Callout 1 SELECT clause
Callout 2 FROM clause
Callout 3 WHERE clause

This example SQL statement reads "Select the data that is stored in the fields named E-mail Address and Company from the table named Contacts, specifically those records in which the value of the field City is Seattle."

Let's look at the example, one clause at a time, to see how SQL syntax works.

The SELECT clause

SELECT [E-mail Address], Company

This is the SELECT clause. It consists of an operator (SELECT) followed by two identifiers ([E-mail Address] and Company).

If an identifier contains spaces or special characters (such as "E-mail Address"), it must be enclosed in square brackets.

A SELECT clause does not have to say which tables contain the fields, and it cannot specify any conditions that must be met by the data to be included.

The SELECT clause always appears in front of the FROM clause in a SELECT statement.

The FROM clause

FROM Contacts

This is the FROM clause. It consists of an operator (FROM) followed by an identifier (Contacts).

A FROM clause does not list the fields to be selected.

The WHERE clause

WHERE City="Seattle"

This is the WHERE clause. It consists of an operator (WHERE) followed by an expression (City="Seattle").

 Note   Unlike the SELECT and FROM clauses, the WHERE clause is not a required element of a SELECT statement.

You can accomplish many of the actions that SQL enables you to do by using SELECT, FROM, and WHERE clauses. More information about how you use these clauses is presented in these sections at the end of this article:



Sorting the results: ORDER BY

Like Microsoft Office Excel, Access lets you sort query results in a datasheet. You can also specify in the query how you want to sort the results when the query is run, by using an ORDER BY clause. If you use an ORDER BY clause, it is the last clause in the SQL statement.

An ORDER BY clause contains a list of the fields that you want to use for sorting, in the same order that you want to apply the sort operations.

For example, suppose that you want your results sorted first by the value of the field Company in descending order, and  — if there are records with the same value for Company — sorted next by the values in the field E-mail Address in ascending order. Your ORDER BY clause would resemble the following:

ORDER BY Company DESC, [E-mail Address]

 Note   By default, Access sorts values in ascending order (A-Z, smallest to largest). Use the DESC keyword to sort values in descending order instead.

For more information about the ORDER BY clause, see the topic ORDER BY Clause.

Top of Page Top of Page

Working with summarized data: GROUP BY and HAVING

Sometimes you want to work with summarized data, such as the total sales in a month, or the most expensive items in an inventory. To do this, you apply an aggregate function (aggregate function: A function, such as Sum, Count, Avg, or Var, that you use to calculate totals.) to a field in your SELECT clause. For example, if you want your query to show the count of e-mail addresses listed for each company, your SELECT clause might resemble the following:

SELECT COUNT([E-mail Address]), Company

The aggregate functions that you can use depend on the type of data that is in the field or expression that you want to use. For more information about the available aggregate functions, see the article SQL Aggregate Functions.

Specifying fields that are not used in an aggregate function: The GROUP BY clause

When you use aggregate functions, you usually must also create a GROUP BY clause. A GROUP BY clause lists all the fields to which you do not apply an aggregate function. If you apply aggregate functions to all the fields in a query, you do not have to create the GROUP BY clause.

A GROUP BY clause immediately follows the WHERE clause, or the FROM clause if there is no WHERE clause. A GROUP BY clause lists the fields as they appear in the SELECT clause.

For example, continuing the previous example, if your SELECT clause applies an aggregate function to [E-mail Address] but not to Company, your GROUP BY clause would resemble the following:

GROUP BY Company

For more information about the GROUP BY clause, see the topic GROUP BY Clause.

Limiting aggregate values by using group criteria: the HAVING clause

If you want to use criteria to limit your results, but the field that you want to apply criteria to is used in an aggregate function, you cannot use a WHERE clause. Instead, you use a HAVING clause. A HAVING clause works like a WHERE clause, but is used for aggregated data.

For example, suppose that you use the AVG function (which calculates an average value) with the first field in your SELECT clause:

SELECT COUNT([E-mail Address]), Company

If you want the query to restrict the results based on the value of that COUNT function, you cannot use a criteria for that field in the WHERE clause. Instead, you put the criteria in a HAVING clause. For example, if you only want the query to return rows if there are more than one e-mail addresses associated with the company, the HAVING clause might resemble the following:

HAVING COUNT([E-mail Address])>1

 Note   A query can have a WHERE clause and a HAVING clause — criteria for fields that are not used in an aggregate function go in the WHERE clause, and criteria for fields that are used with aggregate functions go in the HAVING clause.

For more information about the HAVING clause, see the topic HAVING Clause.

Top of Page Top of Page

Combining query results: UNION

When you want to review all the data that is returned by several similar select queries together, as a combined set, you use the UNION operator.

The UNION operator lets you combine two SELECT statements into one. The SELECT statements that you combine must have the same number of output fields, in the same order, and with the same or compatible data types. When you run the query, data from each set of corresponding fields is combined into one output field, so that the query output has the same number of fields as each of the select statements.

 Note   For the purposes of a union query, the Number and Text data types are compatible.

When you use the UNION operator, you can also specify whether the query results should include duplicate rows, if any exist, by using the ALL key word.

The basic SQL syntax for a union query that combines two SELECT statements is as follows:

SELECT field_1
FROM table_1
UNION [ALL]
SELECT field_a
FROM table_a
;

For example, suppose that you have a table named Products and another table named Services. Both tables have fields that contain the name of the product or service, the price, warranty or guarantee availability, and whether you offer the product or service exclusively. Although the Products table stores warranty information, and the Services table stores guarantee information, the basic information is the same (whether a particular product or service includes a promise of quality). You can use a union query, such as the following, to combine the four fields from the two tables:

SELECT name, price, warranty_available, exclusive_offer
FROM Products
UNION ALL
SELECT name, price, guarantee_available, exclusive_offer
FROM Services
;

For more information about how to combine SELECT statements by using the UNION operator, see the article Combine the results of several select queries by using a union query.

Top of Page Top of Page

More about the SELECT clause

In a SELECT statement, the SELECT clause lists the fields that contain data that you want to use.

Use square brackets to enclose identifiers

You can use square brackets to enclose the name of a field in a SELECT clause. If the name does not contain any spaces or special characters (such as punctuation marks), the square brackets are optional. If the name does contain spaces or special characters, you must use the brackets.

 Tip   A name that contains spaces is easier to read and can save you time when you design forms and reports, but may end up making you type more when you write SQL statements. You should consider this fact when you name objects in your database.

If your SQL statement has two or more fields that have the same name, you must add the name of each field's data source to the field name in the SELECT clause. You use the same name for the data source that you use in the FROM clause.

Select all fields

When you want to include all the fields from a data source, you can either list all the fields individually in the SELECT clause, or you can use the asterisk wildcard character (*). When you use the asterisk, Access determines when the query is run what fields the data source contains, and includes all those fields in the query. This helps make sure that the query stays up-to-date if new fields are added to the data source.

You can use the asterisk with one or more data sources in a SQL statement. If you use the asterisk and there are multiple data sources, you must include the data source name together with the asterisk, so that Access can determine which data source to include all fields from.

For example, suppose you want to select all the fields from the Orders table but only the e-mail address from the Contacts table. Your SELECT clause might resemble this:

SELECT Orders.*, Contacts.[E-mail Address]

 Note   Keep track of when you use the asterisk. If new fields are later added to the data source and you did not plan for them, your query results might not turn out as you want.

Select distinct values

If you know that your statement will select redundant data, and you would rather see only distinct values, you can use the DISTINCT keyword in your SELECT clause. For example, suppose that your customers each represent several different interests, some of which use the same telephone number. If you want to make sure that you only see each telephone number once, your SELECT clause appears as follows:

SELECT DISTINCT [txtCustomerPhone]

Use substitute names for fields or expressions: the AS keyword

You can change the label that is displayed for any field in datasheet view by using the AS keyword and a field alias in your SELECT clause. A field alias is a name that you assign to a field in a query to make the results easier to read. For example, if you want to select data from a field named txtCustPhone, and the field contains customer telephone numbers, you could improve the readability of your results by using a field alias in your SELECT statement, as follows:

SELECT [txtCustPhone] AS [Customer Phone]

 Note   You must use a field alias when you use an expression in a SELECT clause.

Select by using an expression

Sometimes, you want to look at calculations based on your data, or retrieve only part of a field's data. For example, suppose that you want to return the year that customers were born, based on data in the BirthDate field in your database. Your SELECT clause might resemble the following:

SELECT DatePart("yyyy",[BirthDate]) AS [Birth Year]

This expression consists of the DatePart function and two arguments — "yyyy" (a constant), and [BirthDate] (an identifier).

You can use any valid expression as a field, if the expression outputs a single value when given a single input value.

Top of Page Top of Page

More about the FROM clause

In a SELECT statement, the FROM clause specifies the tables or queries that contain the data that the SELECT clause will use.

Suppose that you want to know the telephone number of a specific customer. Assuming that the table that contains the field that stores this data is called tblCustomer, the FROM clause would resemble the following:

FROM tblCustomer

Use square brackets to enclose identifiers

You can use square brackets to enclose the name. If the name does not contain any spaces or special characters (such as punctuation marks), the square brackets are optional. If the name does contain spaces or special characters, you must use the brackets.

 Tip    A name that contains spaces is easier to read and can save you time when you design forms and reports, but may end up making you type more when you write SQL statements. You should consider this fact when you name objects in your database.

Use substitute names for data sources

You can use a different name to refer to a data source in a SELECT statement by using a table alias in your FROM clause. A table alias is a name that you assign to a data source in a query when you use an expression as a data source, or to make the SQL statement easier to type and read. This can be especially useful if the name of the data source is long or difficult to type, especially when there are multiple fields that have the same name from different tables.

For example, if you want to select data from two fields, both named ID, one of which comes from the table tblCustomer and the other from the table tblOrder, your SELECT clause might resemble the following:

SELECT [tblCustomer].[ID], [tblOrder].[ID]

By using table aliases in your FROM clause, you could make the query easier to type. Your FROM clause, with table aliases, might resemble the following:

FROM [tblCustomer] AS [C], [tblOrder] AS [O]

You could then use those table aliases in your SELECT clause, as follows:

SELECT [C].[ID], [O].[ID]

 Note   When you use a table alias, you can refer to the data source in your SQL statement by using the alias or by using the full name of the data source.

Join related data

When you need a way to combine pairs of records from two data sources into single records in a query result, you can perform a join. A join is a SQL operation that specifies how two data sources are related, and whether data from one source should be included if there is no corresponding data from the other source.

To combine the information from two data sources, you perform a join operation on the field that they have in common. When the values stored in this field match, the data from the records is combined in the results.

In addition to combining data, you also use a join to specify whether to include records from either table if there is no corresponding record in the related table.

For example, suppose you want to use data from two tables in a query: tblCustomer and tblOrder. The two tables both have a field, CustomerID, that identifies a customer. Each record in the tblCustomer table may have one or more corresponding records in the tblOrder table, and the corresponding values can be determined by values in the CustomerID field.

If you want to join the tables so that the query combines the records from the tables, excluding records from either table if there is no corresponding record in ther other table, your FROM clause might resemble the following (line break added here for readability):

FROM [tblCustomer] INNER JOIN [tblOrder]
 ON [tblCustomer].[CustomerID]=[tblOrder].[CustomerID]

In Microsoft Office Access, joins occur in the FROM clause of a SELECT statement. There are two types of joins: inner joins and outer joins. The following sections explain these two types of joins.

Inner joins

Inner joins are the most common type of join. When a query with an inner join is run, the only records that are included in the query results are those records where a common value exists in both of the joined tables.

An inner join has the following syntax (line break added here for readability):

FROM table1 INNER JOIN table2
 ON table1.field1 compopr table2.field2

The following table describes the different parts of an INNER JOIN operation.

Part Description
table1, table2 The names of the tables from which records are combined.
field1, field2 The names of the fields that are joined. If they are not numeric, the fields must be of the same data type (data type: The characteristic of a field that determines what type of data it can hold. Data types include Boolean, Integer, Long, Currency, Single, Double, Date, String, and Variant (default).) and contain the same kind of data, but they do not have to have the same name.
compopr Any relational comparison operator: "=," "<," ">," "<=," ">=," or "<>."

Outer joins

Outer joins are similar to inner joins because they tell a query how to combine information from two sources. They are different because they also specify whether to include data where no common value exists. Outer joins are directional: you can specify whether to include all the records from the first data source specified in the join (called a left join), or to include all the records from the second data source in the join (called a right join).

An outer join has the following syntax:

FROM table1 [ LEFT | RIGHT ] JOIN table2
ON table1.field1compopr table2.field2

The following table describes the different parts of LEFT JOIN and RIGHT JOIN operations.

Part Description
table1, table2 The names of the tables from which records are combined.
field1, field2 The names of the fields that are joined. The fields must be of the same data type (data type: The characteristic of a field that determines what type of data it can hold. Data types include Boolean, Integer, Long, Currency, Single, Double, Date, String, and Variant (default).) and contain the same kind of data, but they do not have to have the same name.
compopr Any relational comparison operator: "=," "<," ">," "<=," ">=," or "<>."

For more information about joins, see the article Join tables and queries.

Top of Page Top of Page

More about the WHERE clause

When you want to use data to limit the number of records that are returned in a query, you use query criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.) in the WHERE clause of a SELECT statement. A query criterion is similar to a formula — it is a string that may consist of field references, operators (operator: A sign or symbol that specifies the type of calculation to perform within an expression. There are mathematical, comparison, logical, and reference operators.), and constants (constant: A value that is not calculated and, therefore, does not change. For example, the number 210, and the text "Quarterly Earnings" are constants. An expression, or a value resulting from an expression, is not a constant.). Query criteria are a type of expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.).

The following table shows some sample criteria and explains how they work.

Criteria Description
>25 and <50 This criterion applies to a Number field, such as Price or UnitsInStock. It includes only those records where the field contains a value greater than 25 and less than 50.
DateDiff ("yyyy", [BirthDate], Date()) > 30 This criterion applies to a Date/Time field, such as BirthDate. Only records where the number of years between a person's birth date and today's date is greater than 30 are included in the query result.
Is Null This criterion can be applied to any kind of field to show records where the field value is null.

As the previous table illustrates, criteria can look very different from each other depending on the data type of the field to which the criteria apply and your specific requirements. Some criteria are simple, and use basic operators and constants. Others are complex, and use functions and special operators, and include field references.

 Important   If a field is used with an aggregate function, you cannot specify criteria for that field in a WHERE clause. Instead, you use a HAVING clause to specify criteria for aggregated fields. For more information, see the section Working with summarized data: GROUP BY and HAVING.

WHERE clause syntax

A WHERE clause has the following basic syntax:

WHERE field = criterion

For example, suppose that you want the telephone number of a customer, but you only remember that the customer's last name is Bagel. Instead of looking at all the telephone numbers in your database, you could use a WHERE clause to limit the results and make it easier to find the telephone number that you want. Assuming that last names are stored in a field that is named LastName, your WHERE clause appears as follows:

WHERE [LastName]='Bagel'

 Note   You do not have to base the criteria in your WHERE clause on the equivalence of values. You can use other comparison operators, such as greater than (>) or less than (<). For example, WHERE [Price]>100.

Use the WHERE clause to combine data sources

Sometimes you may want to combine data sources based on fields that have matching data, but have different data types. For example, a field in one table may have a Number data type, and you want to compare that field to a field in another table that has a Text data type.

You cannot create a join between fields that have different data types. To combine data from two data sources based on values in fields that have different data types, you create a WHERE clause that uses one field as a criterion for the other field, by using the LIKE keyword.

For example, suppose that you want to use data from table1 and table2, but only when the data in field1 (a text field in table1) matches the data in field2 (a number field in table2). Your WHERE clause would resemble the following:

WHERE field1 LIKE field2

For more information about how to create criteria to use in a WHERE clause, see the article Examples of query criteria.

Top of Page Top of Page

Try Office 2010 today!

 
 
Applies to:
Access 2010