How To Create A Crosstab Query

By Tom Wickerath, Microsoft Office Access MVP

"The Access 2007 version of this article is in The Toolbox"


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 in a crosstab result.  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.

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."


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.


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:


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:

 

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:

 

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...

 

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.

About the author:

Tom Wickerath is a chemist at The Boeing Company in Seattle, Washington, USA. He works in the Analytical Chemistry Group of Boeing's Materials & Process Technology (M&PT) organization. In the early 1990's, Tom became interested in the use of database technology to avoid many of the inefficiencies involved in using spreadsheets for the analysis of large amounts of data.

Tom has taught Microsoft Access courses to college students for three years at Bellevue Community College in Bellevue, Washington. He has also been an active member of the Pacific Northwest Access Developer's Group (PNWADG) since the days of Access 2.0 and served as an officer of this group from 1998 to 2002. As a longtime Access expert, Tom has given presentations on complex Access subjects, including tools designed for Access developers, to the Seattle Access Group, the PNWADG and to Boeing Company personnel.

Tom has provided his expertise to Microsoft Access developers and users for years in the Microsoft Access Newsgroups in UseNet, and he was awarded MVP for Microsoft Access in April, 2006. Check the Microsoft.Public.Access Newsgroup for Tom's latest expert advice and feel free to post any questions in these Newsgroups.

Special Note From The Author: "If you find that any of these tips save you a lot of time and frustration with your database development, please consider making a tax deductible contribution to the Northwest Kidney Center, located in Seattle, Washington, USA. I've seen first hand how valuable their work is. With your help, they can do more research in the treatment and prevention of kidney disease, provide increased financial assistance to patients in need, and generally make kidney patients' lives more comfortable. You can even use PayPal to make a quick and easy donation online, and you'll feel better knowing that you have contributed to a better quality of life for those in need. Thanks."

-- Tom Wickerath

http://www.nwkidney.org/nkc/howYouCanHelp/donate/index.html

For questions regarding this tutorial, please contact Tom at:



Tom does not accept unsolicited requests for help. Contact Tom only if you have questions or feedback on one of his articles or tips, or you have been specifically invited by Tom in a newsgroup posting to contact him. If your question references a question in the newsgroups, please include the URL to the post.

Links >>>  Home | Search | Workbench | Orders | Newsletter | Access Security | Access professionals