Export (0) Print
Expand All

SELECT Statement (Microsoft Access SQL)

Office 2007

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:

PartDescription
predicateOne 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.
tableThe name of the table containing the fields from which records are selected.
field1, field2The 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, alias2The names to use as column headers instead of the original column names in table.
tableexpressionThe name of the table or tables containing the data you want to retrieve.
externaldatabaseThe 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.

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

intFldLen
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
See Also

ALL DISTINCT, DISTINCTROW, TOP Predicates (Microsoft Access SQL)

DELETE Statement (Microsoft Access SQL)

FROM Clause (Microsoft Access SQL)

GROUP BY Clause (Microsoft Access SQL)

HAVING Clause (Microsoft Access SQL)

IN Clause (Microsoft Access SQL)

INSERT INTO Statement (Microsoft Access SQL)

ORDER BY Clause (Microsoft Access SQL)

SELECT…INTO Statement (Microsoft Access SQL)

SQL Aggregate Functions (SQL)

UNION Operation (Microsoft Access SQL)

UPDATE Statement (Microsoft Access SQL)

WHERE Clause (Microsoft Access SQL)

WITH OWNERACCESS OPTION Declaration (Microsoft Access SQL)



Community Additions

ADD
Show:
© 2014 Microsoft