FROM Clause

Description

Specifies the tables or queries that contain the fields listed in the SELECT statement.

Syntax

SELECT fieldlist
FROM tableexpression [IN externaldatabase]

A SELECT statement containing a FROM clause has these parts:

Part	Description

fieldlist	The name of the field or fields to be retrieved along with any field-name aliases, SQL aggregate functions, selection predicates (ALL, DISTINCT, DISTINCTROW, or TOP), or other SELECT statement options.
tableexpression	An expression that identifies one or more tables from which data is retrieved. The expression can be a single table name, a saved query name, or a compound resulting from an INNER JOIN, LEFT JOIN, or RIGHT JOIN.
externaldatabase	The full path of an external database containing all the tables in tableexpression.

Remarks

FROM is required and follows any SELECT statement.

The order of the table names in tableexpression isn't important.

For improved performance and ease of use, it's recommended that you use a linked table instead of an IN clause to retrieve data from an external database.

The following example shows how you can retrieve data from the Employees table:

SELECT LastName, FirstName
FROM Employees;

See Also

ALL, DISTINCT, DISTINCTROW, TOP predicates, IN clause, INNER JOIN operation, LEFT JOIN, RIGHT JOIN operations, SELECT statement, WHERE clause.

Specifics (Microsoft Access)

If you include a query or table name in the FROM clause of an SQL statement, that query or table will be automatically added to the Query window.

Conversely, any table or query that you add to the Query window will be included in the FROM clause of the corresponding SQL statement.

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

Example (Microsoft Access)

To try the following examples in Microsoft Access, first create a new query in the Northwind sample database. Close the Show Table dialog box without specifying a table or query. Switch to SQL view, paste an individual example into the SQL window, and run the query.

The following example selects the LastName and FirstName fields of all records in the Employees table:

SELECT LastName, FirstName FROM Employees;

The next example selects all fields from the Employees table:

SELECT * FROM Employees;

The following example counts the number of records that have an entry in the PostalCode field and names the returned field Tally:

SELECT Count(PostalCode) AS Tally FROM Customers;

The next example shows what product unit prices would be if each were increased by 10 percent. It doesn't change the existing unit prices in the database.

SELECT ProductName, UnitPrice AS Current, UnitPrice * 1.1
AS ProposedNewPrices FROM Products;

The following example calculates the number of products in the database and the average and maximum unit prices:

SELECT Count(*) AS [Total Products], Avg(UnitPrice)
AS [Average Unit Price], Max(UnitPrice)
AS [Maximum Unit Price] FROM Products;

The next example displays the ProductName and UnitPrice for each record in the Products table. The string "has a unit price of" separates the two fields in the result set.

SELECT ProductName, 'has a unit price of', UnitPrice FROM Products;