SELECT Statement

Description

Instructs the Microsoft Jet database engine to return information from the database as a set of records.

Syntax

SELECT [predicate] { * | table.* | [table.]field1 [AS alias1] [, [table.]field2 [AS alias2] [, ...]]}
FROM tableexpression [, ...] [IN externaldatabase]
[WHERE... ]
[GROUP BY... ]
[HAVING... ]
[ORDER BY... ]
[WITH OWNERACCESS OPTION]

The SELECT statement has these parts:

Part	Description

predicate	One of the following predicates: ALL, DISTINCT, DISTINCTROW, or TOP. You use the predicate to restrict the number of records returned. If none is specified, the default is ALL.
*	Specifies that all fields from the specified table or tables are selected.
table	The name of the table containing the fields from which records are selected.
field1, field2	The names of the fields containing the data you want to retrieve. If you include more than one field, they are retrieved in the order listed.
alias1, alias2	The names to use as column headers instead of the original column names in table.
tableexpression	The name of the table or tables containing the data you want to retrieve.
externaldatabase	The name of the database containing the tables in tableexpression if they are not in the current database.

Remarks To perform this operation, the Microsoft Jet database engine searches the specified table or tables, extracts the chosen columns, selects rows that meet the criterion, and sorts or groups the resulting rows into the order specified.

SELECT statements don't change data in the database.

SELECT is usually the first word in an SQL statement. Most SQL statements are either SELECT or SELECT...INTO statements.

The minimum syntax for a SELECT statement is:

SELECT fields FROM table

You can use an asterisk (*) to select all fields in a table. The following example selects all of the fields in the Employees table:

SELECT * FROM Employees;

If a field name is included in more than one table in the FROM clause, precede it with the table name and the . (dot) operator. In the following example, the Department field is in both the Employees table and the Supervisors table. The SQL statement selects departments from the Employees table and supervisor names from the Supervisors table:

SELECT Employees.Department, Supervisors.SupvName
FROM Employees INNER JOIN Supervisors
WHERE Employees.Department = Supervisors.Department;

When a Recordset object is created, the Microsoft Jet database engine uses the table's field name as the Field object name in the Recordset object. If you want a different field name or a name isn't implied by the expression used to generate the field, use the AS reserved word. The following example uses the title Birth to name the returned Field object in the resulting Recordset object:

SELECT BirthDate
AS Birth FROM Employees;

Whenever you use aggregate functions or queries that return ambiguous or duplicate Field object names, you must use the AS clause to provide an alternate name for the Field object. The following example uses the title HeadCount to name the returned Field object in the resulting Recordset object:

SELECT COUNT(EmployeeID)
AS HeadCount FROM Employees;

You can use the other clauses in a SELECT statement to further restrict and organize your returned data. For more information, see the topic for the clause you're using.

See Also ALL, DISTINCT, DISTINCTROW, TOP predicates, DELETE statement, Execute method ("DAO Language Reference"), Field object ("DAO Language Reference"), FROM clause, GROUP BY clause, HAVING clause, IN clause, INSERT INTO statement, ORDER BY clause, Recordset object ("DAO Language Reference"), SELECT...INTO statement, SQL property ("DAO Language Reference"), UNION operation, UPDATE statement, WHERE clause, WITH OWNERACCESS OPTION Declaration.

Specifics (Microsoft Access)

In Microsoft Access, if you are working in SQL view in the Query window, field names (field1, field2) are used as column headings in Datasheet view. To display a different column heading for a column in the datasheet, use the AS reserved word. If you use the AS reserved word, then the alias1, alias2 arguments provide the column names to use in displaying the retrieved data in Datasheet view. The use of the AS clause is equivalent to setting the Alias property in the field list property sheet in query Design view.

The following example uses the title "Birthday" to head the column in the resulting datasheet:

SELECT [BirthDate] AS Birthday FROM Employees;

When you use aggregate functions or queries that return ambiguous or duplicate field names, you must also use the AS clause to provide an alternate name for the field. The following example creates the column heading "Head Count" in Datasheet view:

SELECT COUNT([EmployeeID])
AS [Head Count] FROM Employees;

If you are working with Data Access Objects (DAO) in Visual Basic code, the field1, field2 arguments are used to name the Field objects in the Recordset object returned by the query. If you include the AS reserved word, then the alias1, alias2 arguments provide the column headings to return as Field object names in the resulting Recordset object.

Example

See the FROM clause example.

Example (Microsoft Access)

See the FROM clause example (Microsoft Access).