Part I: Access Building Blocks
210
FIGURE 5.17
Using the Or operator between fields.
“Or” because criteria are in different rows
Here’s the SQL statement behind the query in Figure 5.17:
SELECT tblCustomers.Company, tblCustomers.State,
tblProducts.Description, tblProducts.Category
FROM tblProducts
INNER JOIN (tblCustomers
INNER JOIN (tblSales INNER JOIN tblSalesLineItems
ON tblSales.InvoiceNumber = tblSalesLineItems.InvoiceNumber)
ON tblCustomers.CustomerID = tblSales.CustomerID)
ON tblProducts.ProductID = tblSalesLineItems.ProductID
WHERE (tblCustomers.State=“CT”) OR (tblProducts.Category=“Trucks”)
Notice the placement of parentheses in the WHERE clause. Either condition (State = “CT” or
Category=“Trucks”) can be true, and the record is returned by the query.
Moving “Trucks” to the same row as “CT” in the QBE grid changes the query’s logic to return cus-
tomers who live in Connecticut and have bought truck models. The rearranged query is shown in
Figure 5.18.
Here’s the SQL statement for this minor rearrangement:
SELECT tblCustomers.Company, tblCustomers.State,
tblProducts.Description, tblProducts.Category
FROM tblProducts
INNER JOIN (tblCustomers
INNER JOIN (tblSales INNER JOIN tblSalesLineItems
ON tblSales.InvoiceNumber = tblSalesLineItems.InvoiceNumber)
ON tblCustomers.CustomerID = tblSales.CustomerID)
ON tblProducts.ProductID = tblSalesLineItems.ProductID
WHERE (tblCustomers.State=“CT”) AND (tblProducts.Category=“Trucks”)
