Microsoft KB Archive/304446

= ACC97: How to Create a Crosstab Query in Microsoft Access =

Article ID: 304446

Article Last Modified on 1/31/2007

-

APPLIES TO


 * Microsoft Access 97 Standard Edition

-



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.

