This is one of a set of articles about Access SQL. This article describes how to write a FROM clause, and uses examples to illustrate various techniques that you can use in a FROM clause.
In a SELECT statement, you specify data sources in the FROM clause. The FROM clause may also contain a JOIN operation. You use a JOIN operation to match and combine data from two data sources, such as two tables, or a table and a query.
For an overview of Access SQL, see the article Access SQL: basic concepts, vocabulary, and syntax.
In this article
Specify data sources
In a SELECT statement, the FROM clause specifies the tables or queries that contain the data that the SELECT clause will use.
Suppose that you want to know the telephone number of a specific customer. Assuming that the table that contains the field that stores this data is called tblCustomer, the FROM clause would resemble the following:
FROM tblCustomer
You can use square brackets to enclose the name. If the name does not contain any spaces or special characters (such as punctuation marks), the square brackets are optional. If the name does contain spaces or special characters, you must use the brackets.
Tip: A name that contains spaces is easier to read and can save you time when you design forms and reports, but may end up making you type more when you write SQL statements. You should consider this fact when you name objects in your Access database.
Use substitute names for data sources
You can use a different name to refer to a table in a SELECT statement by using a table alias in your FROM clause. A table alias is a name that you assign to a data source in a query when you use an expression as a data source, or to make the SQL statement easier to type and read. This can be especially useful if the name of the data source is long or difficult to type, especially when there are multiple fields that have the same name from different tables.
For example, if you want to select data from two fields, both named ID, one of which comes from the table tblCustomer and the other from the table tblOrder, your SELECT clause might resemble the following:
SELECT [tblCustomer].[ID], [tblOrder].[ID]
By using table aliases in your FROM clause, you could make the query easier to type. Your FROM clause, with table aliases, might resemble the following:
FROM [tblCustomer] AS [C], [tblOrder] AS [O]
You could then use those table aliases in your SELECT clause, as follows:
SELECT [C].[ID], [O].[ID]
Note: When you use a table alias, you can refer to the table in your SQL statement by using the alias or by using the full table name.
Join related data
When you need a way to combine pairs of records from two data sources into single records in a query result, you can perform a join. A join is a SQL operation that specifies how two data sources are related, and whether data from one source should be included if there is no corresponding data from the other source.
To combine the information from two data sources, you perform a join operation on the field that they have in common. When the values stored in this field match, the data from the records is combined in the results.
In addition to combining data, you also use a join to specify whether to include records from either table if there is no corresponding record in the related table.
For example, suppose you want to use data from two tables in a query: tblCustomer and tblOrder. The two tables both have a field, CustomerID, that identifies a customer. Each record in the tblCustomer table may have one or more corresponding records in the tblOrder table, and the corresponding values can be determined by values in the CustomerID field.
If you want to join the tables so that the query combines the records from the tables, excluding records from either table if there is no corresponding record in ther other table, your FROM clause might resemble the following (line break added here for readability):
FROM [tblCustomer] INNER JOIN [tblOrder] ON [tblCustomer].[CustomerID]=[tblOrder].[CustomerID]
In Access, joins occur in the FROM clause of a SELECT statement. There are two types of joins: inner joins and outer joins. The following sections explain these two types of joins.
Inner joins
Inner joins are the most common type of join. When a query with an inner join is run, the only records that are included in the query results are those records where a common value exists in both of the joined tables.
An inner join has the following syntax (line break added here for readability):
FROM table1 INNER JOIN table2 ON table1.field1 compopr table2.field2
The following table describes the different parts of an INNER JOIN operation.
|
Part |
Description |
|
table1, table2 |
The names of the tables from which records are combined. |
|
field1, field2 |
The names of the fields that are joined. If they are not numeric, the fields must be of the same data type and contain the same kind of data, but they do not have to have the same name. |
|
compopr |
Any relational comparison operator: "=," "<," ">," "<=," ">=," or "<>." |
Outer joins
Outer joins are similar to inner joins because they tell a query how to combine information from two sources. They are different because they also specify whether to include data where no common value exists. Outer joins are directional: you can specify whether to include all the records from the first data source specified in the join (called a left join), or to include all the records from the second data source in the join (called a right join).
An outer join has the following syntax:
FROM table1 [ LEFT | RIGHT ] JOIN table2ON table1.field1compopr table2.field2
The following table describes the different parts of LEFT JOIN and RIGHT JOIN operations.
|
Part |
Description |
|
table1, table2 |
The names of the tables from which records are combined. |
|
field1, field2 |
The names of the fields that are joined. The fields must be of the same data type and contain the same kind of data, but they do not have to have the same name. |
|
compopr |
Any relational comparison operator: "=," "<," ">," "<=," ">=," or "<>." |
For more information about joins, see the article Join tables and queries.
