The SELECT statement instructs the Microsoft Access 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 Access database engine searches the specified table or tables, extracts the selected columns, chooses rows that meet the criteria, and then sorts or groups the resulting rows into the specified order.
SELECT statements don't change data in the database.
SELECT is usually the first word in a 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 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 Access 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 if the expression doesn't imply a name, 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 the data that is returned. For more information, see the Help topic for the clause that you're using.