MS Access for the Business Environment: Access Query Techniques: Crosstab Queries - Page 5
November 3, 2003
We note that, while the number of records returned remains the same number as we saw retrieved in our last query (89), we now have only three columns; two of the columns ("Produce" and "Seafood") are driven by our request for fixed, existing columns in the dataset. I added the third field (Magazines) to the IN clause to illustrate the manner by which we might add a fixed value that does not exist naturally in the database table, as a means of creating a column in the returned dataset. (Had I enclosed Magazines in quotation marks, then the quotation marks would have been literally presented in the newly created column heading.)
25. Save the query as ACC06-Step3.
In wrapping up our practice example, we will now examine another way to restricting the returned data. This time, we will add an optional PARAMETERS declaration, whereby we create a filter that is selected by the user of the query at run time. The user is prompted, in our example, for the year for which he / she wishes to restrict the query. Many options exist in the typical database for parameters by which to filter via this means.
26. From SQL view add the following, precisely as shown, above the TRANSFORM clause, to the SQL in ACC06-Step3:
PARAMETERS [What Year?] Long;
27. Add the following between the end of the FROM clause (ON Products.ProductID = [Order Details].ProductID) and before the GROUP BY clause:
WHERE DatePart("yyyy", Orders.OrderDate) = [What Year?]
The query should now resemble that shown in Illustration 14.
28. Select Query --> Run from the main menu.
We are prompted for "What Year?"
29. Type 1996 into the Enter Parameter Value dialog, as shown in Illustration 15.
30. Click OK to run the query.
The Enter Parameter Value dialog disappears, and the query is executed, retrieving the filtered dataset.
31. Save the query as ACC06-Step4.
The final dataset appears as partially shown in Illustration 16.
We now see the power of the Year filter: the records are filtered for those containing order dates of 1996 (and, as a quick review of the SQL makes clear, are joined to the fields that we entrain via the SELECT query). We note that the number of records represented by Illustration 16 (66) is a subset of the pre-filter query result dataset that we viewed in Illustration 13 (89).
With even minimal examination, we can discern the utility of the TRANSFORM statement, and of the crosstab query in general, for generating compact, user-friendly presentations of data. There are many other considerations in deciding whether to deploy crosstab functionality, such as the number of records in the underlying tables, the complexity of the underlying SELECT statement and so forth. Executing a crosstab query can cause serious performance degradation on what might be a critical server, among other negatives, depending on these factors. Careful planning would be prudent in a production environment, as always. But, in general, crosstab queries can be of dramatic benefit.
With this lesson we explored the creation and use of an SQL query to render a result dataset in a crosstab format. To this end, we introduced the TRANSFORM statement, then examined the syntax surrounding the use of the statement. We then practiced the construction of crosstab queries within the context of an illustrative, multiple-step example, discussing the relevant characteristics of some of its components to become familiar with crosstab query operation. Finally, we briefly discussed various aspects of the results datasets that we obtained in each step of the practice examples.