This is one of a set of articles about Access SQL. This article describes how to write a SELECT clause, and uses examples to illustrate various techniques that you can use when you write them.
For an overview of Access SQL, see the article Access SQL: basic concepts, vocabulary, and syntax.
In this article
Select fields: the SELECT clause
A SELECT statement usually starts with a SELECT clause. You use a SELECT clause to specify the names of the fields that have data that you want to use in a query. You can also use expressions instead of or in addition to fields. You can even use another SELECT statement as a field — this is referred to as a subquery.
Suppose that you want to know the telephone numbers of your customers. Assuming that the field that stores customer telephone numbers is called txtCustPhone, the SELECT clause appears as follows:
SELECT [txtCustomerPhone]
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 Access 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.
