Example 1

Figure 9-1. Customers table

Figure 9-2. Sales table

Suppose you want to query the Customers table in Figure 9-1 and the Sales table in Figure 9-2 to retrieve customers who purchased product ID CT200 or product ID PO200. Look at the following script:

SELECT CustomerID, Lastname, Firstname
FROM Customers
WHERE CustomerID
IN
(SELECT CustomerID
FROM Sales
WHERE ProductID = 'CT200' OR ProductID = 'PO200'),

This script uses the IN predicate to compare the customer IDs in the Customers table to the customer IDs in the Sales table. The non-correlated subquery is enclosed in parentheses and is processed first. It instructs Microsoft Access to retrieve the customer IDs from the Sales table that have a product ID equal to CT200 or PO200. Moving upward, the next query uses the customer IDs retrieved from the subquery to find a matching customer ID in the Customers table. The CustomerID, Lastname, and Firstname columns from the Customers table are displayed for each matching customer ID value. The Customers and Sales tables are related through the CustomerID column (WHERE CustomerID IN SELECT CustomerID). Figure 9-3 shows the results from the query.

Figure 9-3. Results (output)

Just as this is a bit more complex using SQL, it is also a bit more complex using the Access query grid. The key is to consider the subquery as a second query that is called by the first. So, the inner query becomes the embedded part of the SQL query and is a separate query called by the main query. In other words, we have the following two queries (Figures 9-4 and 9-5)…

Figure 9-4. Query Design view

…as the embedded query that is called by the main query.

Figure 9-5. Query Design view

SELECT CustomerID, Lastname, Firstname
FROM Customers
WHERE CustomerID
NOT IN
(SELECT CustomerID
FROM Sales
WHERE ProductID = 'CT200' OR ProductID = 'PO200'),

In the preceding script the NOT operator is used to instruct Microsoft Access to match any condition opposite of the one defined. Look at the results in Figure 9-6.

Figure 9-6. Results (output)

This operation is a bit more complex using the Access query grid since the only way to perform the NOT IN operation is through an outer join. The embedded query remains the same as the previous example, but the join between it and the Customers table in the main query becomes an outer join with a filter applied to the recordset as follows (Figure 9-7):

Figure 9-7. Query Design view

Example 2

Suppose you want to query the Customers table in Figure 9-1 and the Sales table in Figure 9-2 to retrieve product IDs and dates for products purchased by customers who live in Florida. Look at the following script:

SELECT ProductID, DateSold
FROM Sales
WHERE EXISTS
(SELECT CustomerID
FROM Customers
WHERE Customers.CustomerID = Sales.CustomerID
AND State = 'FL'),

The preceding script uses the EXISTS predicate to check for the existence of a value in the correlated subquery. Remember, correlated queries reference queries outside the subquery and they execute once for each record a referenced query returns. The correlated subquery makes a reference to the above query in the WHERE clause of the subquery (WHERE Customers.CustomerID = Sales.CustomerID). The EXISTS predicate instructs Microsoft Access to retrieve the ProductID and DateSold columns that satisfy the condition in the subquery WHERE clause. Look at the results in Figure 9-8.

Figure 9-8. Results (output)

The following query retrieves product IDs and dates for products not purchased by customers who live in Florida.

SELECT ProductID, DateSold
FROM Sales
WHERE NOT EXISTS
(SELECT CustomerID
FROM Customers
WHERE Customers.CustomerID = Sales.CustomerID
AND State = 'FL'),

Once again, this SQL query can be represented by two Access queries, one of which calls the second.

Figure 9-9. Query Design view

The first query filters all customers not in Florida; the second query takes these customers and determines their orders.

Figure 9-10. Query Design view

Figure 9-11. Query Design view

Example 3

Figure 9-12. Products table

Figure 9-13. Sales table

Suppose you want to query the Products table in Figure 9-12 and the Sales table in Figure 9-13 to display product information on products that have a product ID greater than any product ID sold on February 6, 2007. Look at the following script:

SELECT *
FROM Products
WHERE ProductID > ANY
(SELECT ProductID
FROM Sales
WHERE DateSold = #2/6/07#);

This script combines a comparison operator (>) with the ANY predicate to retrieve records from the main query that are greater than any of the records in the non-correlated subquery. The Products and Sales tables are related through the Product ID column (WHERE ProductID > ANY (SELECT ProductID)). Look at the results in Figure 9-14.

Figure 9-14. Results (output)

Example 4

Figure 9-15. Products table

Suppose you want to query the Products table in Figure 9-15 to retrieve product information on products that have less than 20 items in stock. Look at the following script:

SELECT ProductID, ProductName, InStock, OnOrder
FROM Products
WHERE InStock < ALL
(SELECT InStock
FROM Products
WHERE InStock = 20);

The preceding script combines a comparison operator (<) with the ALL predicate to retrieve records from the main query that are less than all of the records in the non-correlated subquery. Look at the results in Figure 9-16.

Figure 9-16. Results (output)

Example 5

Figure 9-17. Customers table

Figure 9-18. Sales table

Suppose you want to query the Customers table in Figure 9-17 and the Sales table in Figure 9-18 to retrieve the customer ID and date of each customer’s first purchase. Look at the following script:

SELECT CustomerID,
(SELECT MIN (DateSold)
FROM Sales
WHERE Sales.CustomerID = Customers.CustomerID) AS
DateOfFirstPurchase
FROM Customers
ORDER BY CustomerID;

This script nests a correlated subquery within another query. The correlated subquery contains an aggregate function (MIN ()) that retrieves the lowest date in the DateSold column. The correlated subquery makes a reference to the outer query in the WHERE clause of the subquery (WHERE Sales.CustomerID = Customers.CustomerID) and is executed once for every customer retrieved from the Customers table. The comma after the CustomerID column (SELECT CustomerID,) in the outer query instructs Microsoft Access to expect an additional alias column (DateOfFirstPurchase). The alias column is specified after the AS keyword in the script. Look at the result in Figure 9-19.

Figure 9-19. Results (output)

This is one case where it is much easier to use the query grid since we can use the built-in MIN () function with an aggregate query to get the same information.

Figure 9-20. Query Design view

One point that we have made repeatedly in this book is that there are often many ways to achieve the same result. This is a perfect example of that point. On the other hand, it also highlights one of the major differences between looking at queries from the SQL perspective and from the Access perspective. In the SQL realm, things are done one at a time in a logical and concise order. Commands can be nested and combined to produce very specific results. It might not be the easiest or most straightforward approach, but there is a great deal of power in SQL. Access provides a simple, direct method to obtain a specific result. It is easy to use and provides considerable power in a simple grid. But its simplicity also is often its major failing. The limitations of the grid to perform some actions and the inability to know what really is happening in the grid without resorting to the SQL view shows how important it is to understand SQL.

Example 6

Figure 9-21. Products table

Suppose you want to retrieve the second highest price in the Products table in Figure 9-21. Look at the following script:

SELECT MAX (Price) AS SecondHighestPrice
FROM Products
WHERE Price NOT IN
(SELECT MAX (Price) FROM Products);

This script uses the NOT IN predicates to compare the results of the main query to the results of the subquery.

The subquery is processed first. It finds the highest price in the Products table. Moving upward the next query works with the NOT IN keywords to retrieve the highest price not in the result set of the subquery.

Since the subquery can only retrieve one record, the main query is used to retrieve a value NOT IN the result set of the subquery. In turn, the main query retrieves the next highest price in the price column of the Products table. Take a look at the results in Figure 9-22.

Figure 9-22. Results (output)