Chapter 13: Accessing Data with VBA Code
481
When you’re working with more than one table, you can supply a table expression to the FROM
clause to specify how to retrieve data from the multiple tables. The FROM clause is where you set
the relationship between two or more tables for the SELECT statement. The table expression can
be one of three types:l (^) INNER JOIN...ON
l RIGHT JOIN...ON
l (^) LEFT JOIN...ON
Use INNER JOIN...ON to specify the Access default inner or equijoin. To join two tables, you link
them using a field that both tables have in common. For example, the Contacts and Sales tables
have a common field that identifies the buyer. To join the Sales and Contacts tables, the table expres-
sion syntax is as follows (see qryInvoicesAndCustomers in Chapter13.accdb):
SELECT tblSales.InvoiceDate, tblSales.InvoiceNumber,
tblCustomers.Company
FROM tblCustomers
INNER JOIN tblSales
ON tblCustomers.CustomerID = tblSales.CustomerID;
Notice that the FROM clause specifies the first table to use: tblCustomers. Then the INNER
JOIN clause specifies the second table to use: tblSales. Finally, the ON keyword specifies which
field(s) — CustomerID, in this case — are used to join the table. The results of this little query
are shown in Figure 13.4.
FIGURE 13.4
A query using the default INNER JOIN clause