dcsimg

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.


Illustration 9: Juxtaposed SELECT and Crosstab Queries, for Comparative Purposes

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.

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.


Illustration 10: The New Crosstab Query

21.  Select Query --> Run from the main menu.

The resulting dataset appears as partially shown in Illustration 11.


Illustration 11: The Dataset Retrieved by the Crosstab Query (Partial View)

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.


Illustration 12: The Crosstab Query with Modified PIVOT Portion

24.  Select Query --> Run from the main menu.

The resulting dataset appears as partially shown in Illustration 13.


Illustration 13: The Dataset Retrieved by the Restricted Crosstab Query (Partial View)








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers