This article is part of a set about Access SQL. It explains how to write a SELECT clause and shows examples of techniques you can use.
For an overview of Access SQL, see the article Access SQL: basic concepts, vocabulary, and syntax.
In this article
- Select fields: the SELECT clause
- Select all fields
- Select distinct values
- Use substitute names for fields or expressions: the AS keyword
- Select by using an expression
Select fields: the SELECT clause
A SELECT statement usually starts with a SELECT clause. Use the clause to specify the fields that contain the data you want 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 called a subquery.
Suppose you want to know your customers' telephone numbers. If the field that stores those numbers is named txtCustomerPhone, the SELECT clause looks like this:
SELECT [txtCustomerPhone]
You can use square brackets to enclose the name. If the name doesn't contain spaces or special characters, the brackets are optional. If it does, you must use them.
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 with the same name, add each field's data source name in the SELECT clause. Use the same data source name that you use in the FROM clause.
Select all fields
When you want to include all fields from a data source, you can list them individually in the SELECT clause or use the asterisk wildcard character (*). When you use the asterisk, Access determines which fields the data source contains when the query runs and includes them all. This helps keep the query up to date when new fields are added to the data source.
You can use the asterisk with one or more data sources in an SQL statement. If you use the asterisk with multiple data sources, include the data source name with the asterisk so Access can determine which source to use.
For example, suppose you want to select all fields from the Orders table but only the email address from the Contacts table. Your SELECT clause might look like this:
SELECT Orders.*, Contacts.[E-mail Address]
Note
Keep track of when you use the asterisk. If someone later adds new fields to the data source and you didn't plan for them, your query results might not be what you want.
Select distinct values
If you know your statement will return duplicate data and you want to see only distinct values, use the DISTINCT keyword in your SELECT clause. For example, if some customer interests use the same telephone number, DISTINCT makes sure that you see each telephone number only once:
SELECT DISTINCT [txtCustomerPhone]
Use substitute names for fields or expressions: the AS keyword
You can change the label 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, you can make the results easier to read by using a field alias:
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 return only part of a field's value. For example, suppose you want to return the year that customers were born based on data in the BirthDate field. Your SELECT clause might look like this:
SELECT DatePart("yyyy",[BirthDate]) AS [Birth Year]
This expression uses the DatePart function and two arguments: "yyyy" (a constant) and [BirthDate] (an identifier).
You can use any valid expression as a field if it returns a single value for a single input value.