SELECT statement (Microsoft Access SQL)
Applies to: Access 2013 | Office 2013
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. 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 do not 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 is not 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 Help topic for the clause you are using.
Links provided by the UtterAccess community. UtterAccess is the premier Microsoft Access wiki and help forum.
Example
Some of the following examples assume the existence of a hypothetical Salary field in an Employees table. Note that this field does not actually exist in the Northwind database Employees table.
This example creates a dynaset-type Recordset based on an SQL statement that selects the LastName and FirstName fields of all records in the Employees table. It calls the EnumFields procedure, which prints the contents of a Recordset object to the Debug window.
Sub SelectX1()
Dim dbs As Database, rst As Recordset
' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.mdb")
' Select the last name and first name values of all
' records in the Employees table.
Set rst = dbs.OpenRecordset("SELECT LastName, " _
& "FirstName FROM Employees;")
' Populate the recordset.
rst.MoveLast
' Call EnumFields to print the contents of the
' Recordset.
EnumFields rst,12
dbs.Close
End Sub
This example counts the number of records that have an entry in the PostalCode field and names the returned field Tally.
Sub SelectX2()
Dim dbs As Database, rst As Recordset
' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.mdb")
' Count the number of records with a PostalCode
' value and return the total in the Tally field.
Set rst = dbs.OpenRecordset("SELECT Count " _
& "(PostalCode) AS Tally FROM Customers;")
' Populate the Recordset.
rst.MoveLast
' Call EnumFields to print the contents of
' the Recordset. Specify field width = 12.
EnumFields rst, 12
dbs.Close
End Sub
This example shows the number of employees and the average and maximum salaries.
Sub SelectX3()
Dim dbs As Database, rst As Recordset
' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.mdb")
' Count the number of employees, calculate the
' average salary, and return the highest salary.
Set rst = dbs.OpenRecordset("SELECT Count (*) " _
& "AS TotalEmployees, Avg(Salary) " _
& "AS AverageSalary, Max(Salary) " _
& "AS MaximumSalary FROM Employees;")
' Populate the Recordset.
rst.MoveLast
' Call EnumFields to print the contents of
' the Recordset. Pass the Recordset object and
' desired field width.
EnumFields rst, 17
dbs.Close
End Sub
The Sub procedure EnumFields is passed a Recordset object from the calling procedure. The procedure then formats and prints the fields of the Recordset to the Debug window. The variable is the desired printed field width. Some fields may be truncated.
Sub EnumFields(rst As Recordset, intFldLen As Integer)
Dim lngRecords As Long, lngFields As Long
Dim lngRecCount As Long, lngFldCount As Long
Dim strTitle As String, strTemp As String
' Set the lngRecords variable to the number of
' records in the Recordset.
lngRecords = rst.RecordCount
' Set the lngFields variable to the number of
' fields in the Recordset.
lngFields = rst.Fields.Count
Debug.Print "There are " & lngRecords _
& " records containing " & lngFields _
& " fields in the recordset."
Debug.Print
' Form a string to print the column heading.
strTitle = "Record "
For lngFldCount = 0 To lngFields - 1
strTitle = strTitle _
& Left(rst.Fields(lngFldCount).Name _
& Space(intFldLen), intFldLen)
Next lngFldCount
' Print the column heading.
Debug.Print strTitle
Debug.Print
' Loop through the Recordset; print the record
' number and field values.
rst.MoveFirst
For lngRecCount = 0 To lngRecords - 1
Debug.Print Right(Space(6) & _
Str(lngRecCount), 6) & " ";
For lngFldCount = 0 To lngFields - 1
' Check for Null values.
If IsNull(rst.Fields(lngFldCount)) Then
strTemp = "<null>"
Else
' Set strTemp to the field contents.
Select Case _
rst.Fields(lngFldCount).Type
Case 11
strTemp = ""
Case dbText, dbMemo
strTemp = _
rst.Fields(lngFldCount)
Case Else
strTemp = _
str(rst.Fields(lngFldCount))
End Select
End If
Debug.Print Left(strTemp _
& Space(intFldLen), intFldLen);
Next lngFldCount
Debug.Print
rst.MoveNext
Next lngRecCount
End Sub