MS Access for the Business Environment: Access Query Techniques: Crosstab Queries - Page 4
November 3, 2003
We have assembled the basic SELECT query, and run it to obtain the resulting dataset. Our objective in doing this is to be able to see the differences in the SELECT query and the crosstab query, which is essentially a "transformed" SELECT query that calculates and restructures the SELECT query dataset into a more useful presentation layout.
Notice that our SELECT query dataset only groups the totals vertically by customer company name and product category. This results in multiple records in many cases, making comparisons between different customers' total products more difficult. It is even more difficult to readily tell at a glance the composition of each customer's activity, totaled by product category, or even to easily discern a total quantity of products. The database with which we are practicing is quite small - if we can extrapolate this same scenario to a much larger database, it is easy to see why the simple SELECT query dataset might not meet our needs in a user-friendly way.
A crosstab query displays the same information, but groups it both horizontally and vertically so the datasheet is more compact and easier to analyze. We shall see the effect in our next steps, as we use the TRANSFORM statement to present our data in a crosstab, grouping by product category horizontally and by customer vertically.
15. Ensure that the query is saved as ACC06-Step1.
16. Shift back to SQL view, if necessary.
17. Skip a couple of lines below the existing query, and type a dotted line approximately the width of the query.
18. Skip a couple lines below the dotted line, and input the following crosstab query exactly as shown:
TRANSFORM Count(Products.ProductID) AS Qty SELECT Customers.CompanyName FROM (Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID) border=0 alt=""> INNER JOIN ((Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID GROUP BY Customers.CompanyName PIVOT Categories.CategoryName;
The query should now resemble that shown in Illustration 9.
My intent in adding the TRANSFORM statement below the original SELECT query, after the "dividing line," is simply to offer a platform whereby we might see the two juxtaposed briefly, to allow for a comparison. Obviously we would not attempt to run the query in its present state.
We can see the rearrangement of the upper SELECT query into the lower TRANSFORM statement's layout: First, the TRANSFORM statement applies the COUNT aggregate function. We see that the TRANSFORM statement appears before the SELECT statement that it is "transforming."
19. Delete the top half of the existing text in the query - down to and including, the dotted line. Align the new crosstab query (from TRANSFORM down) to the top of the SQL view editor window, as shown in Illustration 10.
20. Save the query as ACC06-Step2.
The query should now resemble that shown in Illustration 10.
21. Select Query --> Run from the main menu.
The resulting dataset appears as partially shown in Illustration 11.
We can see that the crosstab query returns a list of different Customers with product quantities (counts), summarized by product category, for each. As we have noted, the PIVOT is pivoting over Category Name, presenting an individual column for which sales occurred. We also note that there are a total of 89 records.
Let's take a look at restricting the data retrieved with an IN clause addition to the PIVOT clause.
22. Shift back to SQL view, once again, if necessary.
23. Add the following between the end of the existing PIVOT line of the query and the ";" character at its end:
IN ("Produce", "Seafood", Magazines);
NOTE: Be sure to avoid surrounding Magazines with quotation marks in the added clause. We shall see why momentarily.
The query should now resemble that shown in Illustration 12.
24. Select Query --> Run from the main menu.
The resulting dataset appears as partially shown in Illustration 13.