How To Create A Crosstab Query

<< Click to Display Table of Contents >>

Navigation:  Queries >

How To Create A Crosstab Query

By Tom Wickerath, Microsoft Office Access MVP

Crosstab queries are useful for summarizing information, calculating statistics, spotting bad data and looking for trends. The results of a crosstab query are read-only data cannot be added, edited or deleted.  An aggregate function, such as sum or count, is used to help summarize the data. This article describes how you make them.

Microsoft provides the Northwind database as a source for examples. Make a copy of this database and name it NWind.mdb, so that you can use it for this set of instructions without modifying the original database file.

Click here for the Access 2007 Northwind Database

Open Access Help and type in "Crosstab Queries" to study the examples given. In Access 2000, click "Example of a Crosstab Query," then click the graphic to get the example. In Access 2002 and 2003, click "About select and crosstab queries" and then select "Crosstab queries."

xtab1

Figure 1: Example of a Crosstab Query as Shown in Access 2000

 

As shown above, a salesperson can sell many categories (beverages, condiments and confections). Likewise, a category of product can be sold by more than one salesperson. The employees are listed as the "Row Heading." The categories are listed as the "Column Heading." The totals are listed as the "Value." You are allowed up to three row headings in a crosstab query, but you are limited to a single column heading and value.

 

Study the relationships of all the joined tables in Figure 2 to determine the fields that you will need to use.

 

xtab2

Figure 2: Table Relationships.

 

Note that you should first establish the relationships between your tables to avoid getting a Cartesian product result. A Cartesian product recordset consists of the total number of records selected from each table all multiplied together. This result occurs when relationships have not been established between the tables (or the relationships have been deleted) in the query design.

 

Don't forget to run your query after each step. It will show if you have made a mistake.

1.) Open the Northwind database, select the Queries object in the database window and then click New. Select Design View from the list rather than Crosstab Query Wizard, because we will be building our crosstab query from scratch. (The Crosstab Query Wizard requires that you already have the basic Select query created.)

2.) The Show Table dialog box appears. Select the following five tables:

Categories, Products, Order Details, Orders and Employees.

3.) Choose the Fields that will define the row and column headings:

LastName from the Employees table and

CategoryName from the Categories table.

Note: You can add FirstName either as a second row heading, or concatenated into a single row heading, i.e., Employee: LastName & ", " & FirstName

 

4.) Enter a Calculated Field (Press <SHIFT> + <F2> to open a zoom window)

LineItemTotal: ([Order Details!UnitPrice])*[Quantity]*(1-[Discount])

The table name Order Details with an exclamation mark (!) must be included in the calculated field, so that Access will know which UnitPrice to use, since a field with the same name is used in the Products table. Place a check mark in the QBE grid "Show" box to display this result.

Notes: UnitPrice should be selected from the Order Details table, rather than the Products table, because this reflects the price at the time the sale was made.

Good database design dictates that field names be unique in a database ** If this design principle had been followed in Northwind, there would be no need to specify the table name in the calculated field.

5.) To format this field, right click the calculated Field and click Properties. Under the General tab, click in Format, and in the drop down list, click Currency. As an alternative, you can use the CCur type conversion function to display a currency result. See Access Help for more information on type conversion functions if you are interested.

LineItemTotal: CCur (([Order Details!UnitPrice])*[Quantity]*(1-[Discount]))

Run the query. You should see 2155 records. Save your work now as qxtbEmployeeSales.

Notes: We are using the prefix "qxtb" to denote a crosstab query, even though our query is only a select query at this point. Alternatively, you can save the query as a normal select query, using the prefix "qry," and then use this new query in the Crosstab Query Wizard. However, you will end up with a query that uses another query as its recordsource if you use the Crosstab Query Wizard to finish the process.

6.) Click the Totals button on your toolbar (or use the menu to click on View > Totals). Select Sum in the Group By in the QBE grid for the LineItemTotal field. Run the query. You should see 72 records.

7.) For the Criteria in the Field CategoryName, type:

"Beverages" Or "Condiments" Or "Confections"

or type:

In ("Beverages", "Condiments", "Confections")

Run the query. You should see 27 records.

Note: Although there are additional categories, our goal at this point is to match the result shown in Figure 1 as closely as possible.

Now you are ready to do your crosstab.

8.) In Design View, click Query on the menu bar and then click Crosstab Query. You should see a new row in the QBE grid titled "Crosstab."

9.) Select Row Heading in the Crosstab row under the LastName field.

10.) Select Column Heading in the Crosstab row under CategoryName field.

11.) Select Value in the Crosstab row under the Calculated Field. (You must have a value specified in a crosstab query.)

12.) Run your query. It should look like Figure 3:

 

xtab3

Figure 3: Crosstab Query Results.

 

13.) Save your query one more time.

Notes: The results shown above come from a "clean" copy of the Northwind sample database (i.e., no records have been added or deleted). The results shown in Figure 1 indicate higher sales amounts than these results. Apparently, the person who wrote this portion of the Help file was using creative accounting techniques from Enron — they were not using a clean copy of Northwind.

14.) You can add another column that displays a sum for each employee's sales for all categories of product sold. Enter this new column in the QBE grid as indicated below:

 

 

Field:

Total Sales: [LineItemTotal]

 

Total:

Sum

 

Crosstab:

Row Heading

 

15.) Now add any additional criteria that you might want. For example, you could add the OrderDate field from the Orders table, and include a criteria such as:

 

 

Field:

OrderDate

 

Table:

Orders

 

Total:

Remove Group By and leave this cell blank

 

Criteria:

BETWEEN #1/1/1997# AND #12/31/1997#

 

You may also use a parameter query to allow the user to select the dates. In order to get prompted for parameters in a crosstab query, you must enter the parameter prompts exactly as shown in the criteria line by using the menu to select Query > Parameters...

 

 

Parameter

Data Type

 

[Enter Beginning Date]

Date / Time

 

[Enter Ending Date]

Date / Time

 

 

 

 

Valid order dates for the Access 2000 through 2003 versions of the Northwind database include the range between 7/4/1996 to 5/6/1998.

If you encounter an error while running the crosstab query with your own defined parameter, please consult the following Microsoft Knowledge Base article: "Error When Running Crosstab Query With A Parameter."

For additional information on crosstab queries, you can also read the following Microsoft Knowledge Base article: "HOW TO: Create a Crosstab Query in Microsoft Access 2000."

 

Summary

The crosstab query provides an excellent method for displaying the summaries or aggregates of your data, making it easier to identify trends or even recognize inaccurate data. Before creating the crosstab query, make sure that you have already established relationships between the tables to avoid a Cartesian product result.

The crosstab query is derived from a select query. If you choose to use the Crosstab Query Wizard to create your crosstab query, then you must create the select query before using the wizard.

The crosstab query may contain fields for up to three row headings, but may contain only one field and value for the column heading. In addition to the fields available from the select query, you may also add calculated columns to the crosstab query to further customize your query.

By default, the column headings for a crosstab query are sorted in alphabetical or numerical order, depending upon the data type. If you need these column headings to appear in a chronological order (January, February, March, et cetera, for example, and not the default April, August, December, et cetera), or if you need to limit the column headings that will be displayed, then you can use "fixed headings" by setting the "ColumnHeadings" Property in the query's property sheet.

However, each time changes need to be made, the settings for these column headings will have to be set again manually. For example, the year-to-date crosstab query for March would contain the column headings "January,February,March" and in April, the column heading "April" would have to be added to the original. In May, the column heading "May" would have to be added, and so on.

You can avoid this manual labor by using a "self-adjusting" or dynamically modified query. This discussion is for another day.

Created: Jan. 2003

Last Updated: May 5th, 2008

Copyright © 2004 - 2008 Tom Wickerath. All rights reserved. Reprinted at vb123.com by permission.

 

Other Articles You May Like
 
Analyze Your Data in Space