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.

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."
 
After the mandatory SELECT statement, which, as we can easily note in our arrangement from above, is identical to the initial SELECT query (including the GROUP BY clause) , we can see the PIVOT clause, where we have placed the Categories.CategoryName field that once existed in the first line of the SELECT query. Recall that the PIVOT clause determines the column names for the crosstab - in our case, it sets up a column for every existing Category Name in the dataset under consideration.

The query should now resemble that shown in Illustration 10.

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.

IN ("Produce", "Seafood", Magazines);

The query should now resemble that shown in Illustration 12.

The resulting dataset appears as partially shown in Illustration 13.