Access Structured Query Language (SQL) - Access Database Design and Programming - page 103

which gives the result table shown in Table 6-16.

Table 6-16. Aggregating results in a crosstab table

Price Count Sum Big House Medium House Small House

$ 12.00 2 $24.00 2

$15.00 1 $15.00 1

$ 20.00 2 $40.00 1 1

$25.00 2 $50.00 2

$ 34.00 1 $34.00 1

$49.00 3 $147.00 1 1 1

Finally, by including fixed column names, we can reorder or omit columns from the
crosstab result table. For instance, the next statement is just like the previous one except
for the PIVOT clause:

TRANSFORM COUNT(Title)
SELECT Price, COUNT(Price) AS Count, SUM(Price) AS Sum
FROM PUBLISHERS INNER JOIN BOOKS
ON PUBLISHERS.PubID=BOOKS.PubID
GROUP BY Price
PIVOT PubName IN ("Small House", "Medium House");

The result table is shown in Table 6-17. Note that the order of the columns has changed
and Big House is not shown.

Tabl e 6-17. Omittin g columns from a cross t ab table

Price Count Sum Small House Medium House

$ 12.00 2 $ 24.00 2

$15.00 1 $15.00

$ 20.00 2 $ 40.00 1

$ 25.00 2 $ 50.00

$ 34.00 1 $ 34.00 1

$ 49.00 3 $ 147.00 1 1

6.7.10 Subqueries

SQL permits the use of SELECT statements within the following:

Other SELECT statements

•

SELECT... INTO statements

•

INSERT... INTO statements

•

DELETE statements

•

UPDATE statements

•

The internal SELECT statement is referred to as a subquery and is generally used in the
WHERE clause of the main query.