Part II: Programming Microsoft Access
482
In the case of an inner join, it really makes no difference which table is specified first in the FROM
clause. Because records are selected only when values exist on both sides of the join (for example,
when CustomerID joins tblCustomers and tblSales), Access gets data from both tables,
regardless of which table is specified in the FROM clause.Note
In Figure 13.4 notice that the Customer ID is not shown in the query results. There is no need to display the
Customer ID in this particular query because, even though the Customer ID is used as the join field, it isn’t
required by the users in the query’s output.
The inner join requirement that the same value (in our case, the same CustomerID value)
appears in both tables causes a lot of trouble for Access developers. Because matching records must
appear in both tables before data from either table appears in the query’s results, always keep in
mind that the default join in an Access query is an inner join, and it’s possible that more records
are available in the tables than are indicated by the query results.The LEFT JOIN and RIGHT JOIN work exactly the same, except that they specify an outer join
instead of an inner join. You use outer joins when you want to return records from a parent table
even if the dependent table does not contain any records with matching values specified in the ON
clause. The following example (qryCustomersAndInvoiceNumbers) shows a query coded as
an outer join:SELECT tblCustomers.Company, tblSales.InvoiceNumber
FROM tblCustomers
LEFT JOIN tblSales
ON tblCustomers.CustomerID = tblSales.CustomerID;In this example, the query includes all the company names and the invoice numbers associated with
customers. All company names are included in the results, even those that have not placed an order.
The Invoice Number field is null when the customer has not yet placed a sale (see Figure 13.5).Most of the rows in Figure 13.5 include an InvoiceNumber value, but there are several null cells
in the query’s datasheet. These rows indicate customers who are in the Customers table but haven’t
placed orders.If you’d like to see all customers who haven’t placed a sale, add a filter to the query’s design. In this
case (qryCustomersWithNoInvoiceNumbers), the query selects records where the
InvoiceNumber is Null:SELECT tblCustomers.Company, tblCustomers.Address,
tblCustomers.City, tblCustomers.State,
tblCustomers.ZipCode, tblCustomers.Phone
FROM tblCustomers
LEFT JOIN tblSales
ON tblCustomers.CustomerID = tblSales.CustomerID
WHERE (((tblSales.InvoiceNumber) Is Null));