MS Access for the Business Environment: Access Query Techniques: Crosstab Queries

About the Series …

This article continues the series, MS Access for the Business Environment. The primary focus of this series is an examination of business uses for the MS Access relational database management system. The series is designed to provide guidance in the practical application of data and database concepts to meet specific needs in the business world. While the majority of the procedures I demonstrate will be undertaken with MS Access 2002, many of the concepts that we expose in the series will apply to other versions of MS Access.

For more information on the series, as well as the hardware / software requirements to prepare for the tutorials we will undertake, please see Tutorial 1: Create a Calculated Field with the Expression Builder.

Introduction to this Tutorial

As many of us know from general business experience, the crosstab format is popular for data presentation among a wide range of information consumers. The typical objective of a crosstab query is to display summarized values from one field in a table, grouping them by one set of dimensions listed down the left side of the dataset and another set of dimensions listed across the top of the dataset.

While we can certainly rely upon the Crosstab Query Wizard to guide us through the creation of a basic crosstab query, the objective of this tutorial is to explore the creation and use of an SQL query to render a result dataset in a crosstab format. To accomplish this, we will introduce the TRANSFORM statement, and then use it in a multiple-step practice example to become familiar with its operation.

Along with an introduction to the TRANSFORM statement, this lesson will include:

  • an examination of the syntax surrounding the use of the statement;
  • illustrative examples of the use of the statement in a multi-step practice exercise that constructs a crosstab query;
  • a brief discussion of the results datasets that we obtain in each step of the practice example.

    We will use the Northwind sample database that installs with MS Access for purposes of this lesson, in an attempt to make the lesson useful to virtually anyone who has access to MS Access.

    The TRANSFORM Statement

    When we summarize data using a crosstab query, we select and position values, from specified fields or expressions, as column headings to obtain a results dataset that is presented in a more compact format than what we would see with a standard SELECT query. The TRANSFORM statement puts into play an aggregate function within a SELECT statement to bring about the generation of the crosstab query.

    We will examine the syntax of the TRANSFORM statement to gain an understanding of its components, and then we will build a crosstab query. Starting with a basic SELECT statement, we will add the TRANSFORM statement in multiple steps to illustrate its use in reaching our end objective.

     

    Discussion

    As we noted in the introduction, a crosstab query usually displays summarized values, or aggregates, from one field in a table. The crosstab query generates a table that has both a row header and a column header; it groups the aggregates by a set of dimensions on the left-axis, and another set of dimensions listed across the top of the dataset. The resulting aggregate data is therefore listed in the table under a cross-reference between these row and column headers – an intersect point, in other words. This is a convenient way to display data in a compact, summarized format.

    The available aggregate functions, which are used to calculate statistical information when applied to a specified numeric field in the SELECT query, include the functions shown in Table 1.

     

    FUNCTION

     

    OUTPUT

         

    AVG

     

    Calculates the arithmetic mean of a specified set of values

    COUNT

     

    Calculates the number of records returned by a query

    MAX

     

    Returns the maximum of a set of specified values or a table

    MIN

     

    Return the minimum of a set of specified values or a table

    STDEV

     

    Estimates standard deviation, a measure of how widely values are dispersed from a mean, based upon a sample of a population

    STDEVP

     

    Estimates standard deviation, a measure of how widely values are dispersed from a mean, based upon a population

    SUM

     

    Returns the sum of a set of values contained in a specified field

    VAR

     

    Returns estimates of the variance for a specified population sample

    VARP

     

    Returns estimates of the variance for a specified population

    Table 1: Aggregate Functions

    We most often see the COUNT() and SUM() functions used in cases where the SELECT query is being subjected to TRANSFORM.

    When we use the TRANSFORM statement to convert a standard SELECT query to a crosstab query, we specify the fields we want to display as row headings, together with using a GROUP BY clause that specifies row groupings. We specify the column headings with a PIVOT operator, and have the option of using an IN clause to limit those headings to fixed values. Finally, we can include other clauses, such as WHERE, as in any SELECT query, to specify additional selection or sorting criteria.

    We will see how a simple SELECT statement constructed for the Northwind database can be converted to a crosstab query, and how we can leverage the components of the TRANSFORM statement, in the steps of the practice example that follow.

    Syntax

    Syntactically, the TRANSFORM statement is used in the following basic manner:

    PARAMETERS [prompt] type;

    TRANSFORM aggregate function
    SELECT select statement

    PIVOT pivotfield [IN (value1 [, value2 [, …] ] ) ]

    The TRANSFORM statement contains the components described in Table 2:

    Component

    Description

    PARAMETERS declaration (optional)

    The optional PARAMETERS declaration allows us to specify a prompt that we wish to appear at run time, as well as to specify the data type of the prompt input.

    aggregate function

    An SQL aggregate function that operates on the selected numerical field values. The subtotals generated appear under column headings in the result dataset.

    select statement

    A SELECT statement.

    The SELECT statement may contain the following additional clauses (of the following, only GROUP BY is required):

    • WHERE (optional) – Restricts the results dataset to a specified subset
    • GROUP BY (mandatory) – Enforces aggregation within row headings as selected in the specified fields list of the SELECT statement
    • ORDER BY (optional) – specifies the ordering of the row headings

    pivotfield

    Defines the field or expression used in creating column headings in the query’s result dataset.

    IN Predicate (value1, value2, etc.)

    (optional)

    Value1, value2, etc. comprise a comma-separated list of desired column headings. The optional IN predicate, followed by our specified list of column headings, provides us the flexibility to show, or otherwise control order and appearance of, specific fields.

    Table 2: Transform Statement Components

    AS we have already seen, the TRANSFORM statement needs to come before the SELECT query in the SQL string. The SELECT statement specifies the fields used as row headings, and contains a GROUP BY clause that specifies row grouping. (The SELECT query can be quite sophisticated; we can add subqueries, for example, as predicates in the WHERE clause within our crosstab queries.

    The pivotfield values appear as column headings in the query’s result dataset. A common example we see in the business world might be pivoting expense values on the month of the posting to generate twelve columns in the crosstab’s result dataset – one for each month of the year (assuming that transactions had occurred on the specified expense accounts during each of the months). To carry this simple illustration a step further, we could easily restrict the results to only show the first quarter of the year by specifying months 1, 2, and 3, in an optional IN predicate. This would constrain the pivotfield to generate headings solely from the fixed values of the first three months of the year.

    I like to base whole reports off crosstab queries, in many instances. In such a scenario, little more than formatting need be accomplished within the reporting application. This can mean that we leave the “heavy lifting” involved in the report to the db engine, which is often the more optimal place for this to be handled.

    Let’s move into a hands-on illustration to reinforce our understanding of crosstab queries, using the Northwind sample database that accompanies a typical installation of MS Access.

William Pearson
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles