ACC97: How to Create a Crosstab Query in Microsoft Access (304446)


The information in this article applies to:

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

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

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.

MORE INFORMATION

CAUTION: If you follow the steps in this example, you modify the sample Access project NorthwindCS.adp. You may want to back up the NorthwindCS.adp file and follow these steps on a copy of the project.

Creating a Crosstab Query by Using the Crosstab Query Wizard

  1. Start Microsoft Access, and then open the sample database Northwind.mdb.
  2. On the View menu, point to Database Objects, and then click Queries.
  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 in the list of tables, and then click Next.
  6. Double-click EmployeeID in the list of Available Fields to add it to the list of Selected Fields, and then click Next. This will create 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 will create a query with one column per year for order dates.
  9. Click OrderID in 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.

Creating a Crosstab Query Without Using a Wizard

  1. Start Microsoft Access, and then open the sample database Northwind.mdb.
  2. On the View menu, point to Database Objects, and then click Queries.
  3. On the Insert menu, click Query.
  4. In the New Query dialog box, click Design View, and then click OK.
  5. In the Show Table dialog box, click the Orders table, click Add, and then click Close.
  6. In the field list of the Orders table, double-click each of the following fields to add the fields to the query design grid: ShipCountry, ShipCity, ShipVia, and Freight.
  7. On the Query menu, click Crosstab Query. Note that a row named Crosstab is added to the query design grid.
  8. Click in the Crosstab row cell under the ShipCountry column in the query design grid, click the arrow that appears, and then click Row Heading
  9. Click in the Crosstab row cell under the ShipCity column in the query design grid, click the arrow that appears, and then click Row Heading.
  10. Click in the Crosstab row cell under the ShipVia column in the query design grid, click the arrow that appears, and then click Column Heading.
  11. Click in the Crosstab row cell under the Freight column in the query design grid, click the arrow that appears, and then click Value. Note that only one field can be set to Value.
  12. Click in the Total row cell under the Freight column in the query design grid, click the arrow that appears, and then click Sum.
  13. On the Query menu, click Run to view the results of the crosstab query.

    Note that the query returns the total freight by country and city for each shipper.

REFERENCES

For more information about creating queries, click Microsoft Access Help on the Help menu, type create a 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 article number below to view the article in the Microsoft Knowledge Base:

109939 ACC: Creating a Crosstab Query with Multiple Value Fields

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

304444 ACC97: How to Create a Select Query in Microsoft Access

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

304447 ACC97: How to Create a Parameter Query in Microsoft Access

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

304448 ACC97: How to Convert a Select Query to an Action Query

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

304451 ACC97: How to Modify Query Properties in Microsoft Access

You can also view queries for most of the techniques described in these articles in the sample file QrySmp97.exe from the following article:

182568 ACC97: Microsoft Access 97 Sample Queries Available in Download Center

Modification Type:MajorLast Reviewed:9/25/2003
Keywords:kbhowto KB304446
©2003 Microsoft Corporation. All rights reserved.