Leadership

How do I... create a crosstab query in Microsoft Access?

Crosstab queries in Microsoft Access allow you to do what normal SELECT queries do not: aggregate data across columns as well as rows. This walkthough and sample database will show you how to use these unique queries to present data in a clear, understandable format.

This article is also available as a PDF download, along with a sample database.

Crosstab queries can be useful when you need to cross-reference grouped data and summarize those groupings in a meaningful way. You may not need them on an everyday basis, but they provide a handy solution in certain situations. Let's look at what's involved in creating these unique queries. Then, we'll work though an example to see one in action.

What is a crosstab query?


Crosstab queries are data retrieval queries used primarily for reporting purposes. They differ from typical SELECT queries in Microsoft Access in that they can pivot aggregated row data into a columnar format. The one prerequisite to this query is that the data must be aggregated in some fashion. The results from these queries are somewhat similar to spreadsheets in their format. However, the meaningful data is represented in columns, rather than rows.

The crosstab rundown


The idea of pivoting your data can be somewhat confusing at first, but once you grasp a few simple points, you'll be well on your way to writing your own.

Crosstab queries require a little bit of planning. That's not to say that all queries don't require some type of planning, but crosstab query design is somewhat different, and you need to follow a couple of rules when you create them:

  • Crosstab queries are always aggregated. The aggregations must be performed to provide summary data based upon required groupings for the crosstab format.
  • When you perform a crosstab query, you must provide at least three forms of crosstab option information. You must specify one or more Row Headings, one Column Heading, and one Value option.

Row Heading: This crosstab option is represented vertically in your dataset. Good candidates for this grouping are product types or other categories of data you want to aggregate. You can have multiple row heading columns, so multiple column aggregations are allowed.

Column Heading: This crosstab option is represented horizontally in your dataset. Good candidates for this grouping are sales quarters or other categories of data for which you want only one grouping aggregation, because only one column heading is allowed in a crosstab query.

Value: This crosstab option is the data that's typically summarized in your crosstab query. It's the product of cross-referencing your Row Heading(s) and your Column Heading aggregation.

This should give you a reasonable idea of how crosstab queries work, but if you're still a little confused, don't fret. An example will clear things up.

A crosstab example


For this example, we're going to look at is a report regarding company sales transactions. First, we'll need to create a table to hold our sales. We'll call it SalesTransaction. It's a relatively simple table that contains the basic key elements most sales tables have: a type of product, the sale price of the product, and the date of the sales transaction. Figure A shows the design of the table in Microsoft Access. You can download a sample database containing this table if you want to follow along with this example.

Figure A

 

I created dummy data for the SalesTransactions table so that we can produce some queries from it. To create the data, I used what's known as a Cartesian Product (Cross Join). The query named qryCreateSampleData in the sample database will introduce you to this useful tool for creating dummy data for testing purposes.

Now that we have a structure and table to query, we're ready to create our crosstab query. Open a new query window and add the SalesTransactions table to the query designer. Then, navigate to the Query Type drop-down list and select Crosstab Query, as shown in Figure B. You'll notice that when you select this query option, the aggregations are instantly flipped on.

Figure B

 

Our goal is to present to management a report detailing product sales by quarter. Of course, we could present this information through a typical SELECT query; however, the data would be much more difficult to absorb and would probably require some massaging by management to present the numbers in a more meaningful way. This is what's great about crosstab queries: They enable you to present data in a way that's easier to read and understand.

Because we're going to look at product sales by quarter, we need to aggregate data by our ProductType and our TransactionDate columns. How you display the data depends on your preference, but when I'm running crosstab queries based upon quarters, months, or years, I typically like to present date data as column headers, which will leave me to display the product data as a row heading.

Once we've set our Row Heading and Column Heading options, we need to decide which Value crosstab option to use. We'll use the ProductPrice field here because we want to see sales information, which means we'll need to SUM that data.

Figure C shows our query. Notice that we are using two Row Heading crosstab options. Adding the ProductPrice again and summing it as a row heading will actually act as a summary field for the Sales Quarters figures.

Figure C

 

Figure D shows the output from our crosstab query. As you can see, we've accomplished what we set out to do. The TotalSales column is the summation of all four quarters of product sales for the specific ProductType.

Figure D

 

Although crosstab queries aren't appropriate for all situations, they're worth their weight in gold when applicable. They enable you to cross-reference grouped data and summarize those groupings in a meaningful way. Happy querying!

About

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

0 comments