How to create a crosstab query in Access 2002 (304349)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q304349
Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access database (.mdb).

For a Microsoft Access 97 version of this article, see 304446.
For a Microsoft Access 2000 version of this article, see 304348.

IN THIS TASK

SUMMARY

By using crosstab queries, you can create summary views of your data for easier analysis. This article shows you how to create a crosstab query in Microsoft Access with and without using a wizard.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

back to the top

Creating a Crosstab Query by Using the Crosstab Query Wizard

  1. Start Microsoft Access.
  2. On the Help menu, click Sample Databases, and then click Northwind Sample Database. Click OK to the opening splash screen, and then close the Main Switchboard form when it appears.
  3. On the Insert menu, click Query.
  4. In the New Query dialog box, click Crosstab Query Wizard, and then click OK.
  5. On the first page of the Crosstab Query Wizard, click Orders from the list of tables, and then click Next.
  6. Double-click EmployeeID from the list of Available Fields to add it to the list of Selected Fields, and then click Next. This creates a query with one record per employee.
  7. Click OrderDate in the list of fields, and then click Next.
  8. Click Year for the date interval, and then click Next. This creates a query with one column per year for order dates.
  9. Click OrderID from the list of fields, click Count in the list of Functions, and then click Next.
  10. Click Finish on the last page of the wizard. Note that a query is created that displays a count of orders by employee by year.
back to the top

Creating a Crosstab Query Without Using a Wizard

  1. Start Microsoft Access.
  2. On the Help menu, click Sample Databases, and then click Northwind Sample Database. Click OK to the opening splash screen, and then close the Main Switchboard form when it appears.
  3. On the View menu, point to Database Objects, and then click Queries.
  4. On the Insert menu, click Query.
  5. In the New Query dialog box, click Design View, and then click OK.
  6. In the Show Table dialog box, click the Orders table, click Add, and then click Close.
  7. In the field list from the Orders table in the top half of the query design grid, double-click the following fields to add them to the query design grid: ShipCountry, ShipCity, ShipVia, and Freight.
  8. On the Query menu, click Crosstab Query. Note that a row is added to the query design grid named Crosstab.
  9. Click in the Crosstab row cell under the ShipCountry column, click the arrow that appears, and then click Row Heading.
  10. Click in the Crosstab row cell under the ShipCity column, click the arrow that appears, and then click Row Heading.
  11. Click in the Crosstab row cell under the ShipVia column, click the arrow that appears, and then click Column Heading.
  12. Click in the Crosstab row cell under the Freight column, click the arrow that appears, and then click Value. Note that only one field can be set to Value.
  13. Click in the Total row cell under the Freight column, click the arrow that appears, and then click Sum.
  14. On the Query menu, click Run to view the results of the crosstab query.
back to the top


REFERENCES

For more information about creating queries, click Microsoft Access Help on the Help menu, type create a crosstab query in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For additional information about creating crosstab queries with multiple value fields, click the following article number to view the article in the Microsoft Knowledge Base:

304458 How to create a crosstab query with multiple value fields

For additional information about creating select queries, click the following article number to view the article in the Microsoft Knowledge Base:

304325 How to create a select query in Access 2002

For additional information about creating parameter queries, click the following article number to view the article in the Microsoft Knowledge Base:

304352 HOW TO: Create a Parameter Query in Microsoft Access 2002

For additional information about how to convert a select query to an action query, click the following article number to view the article in the Microsoft Knowledge Base:

304354 HOW TO: Convert a Select Query to an Action Query in Access

For additional information about modifying query properties, click the following article number to view the article in the Microsoft Knowledge Base:

304356 How to modify query properties in Microsoft Access

back to the top
Modification Type:MajorLast Reviewed:8/12/2004
Keywords:kbQuery kbHOWTOmaster KB304349 kbAudITPro
©2004 Microsoft Corporation. All rights reserved.