Microsoft KB Archive/304348

= HOW TO: Create a Crosstab Query in Microsoft Access 2000 =

Article ID: 304348

Article Last Modified on 8/9/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q304348



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 2002 version of this article, see 304349.

IN THIS TASK
SUMMARY
 * Creating a Crosstab Query by Using the Crosstab Query Wizard
 * Creating a Crosstab Query Without Using a Wizard

REFERENCES



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, 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 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, 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 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.
 * 7) On the Query menu, click Crosstab Query. Note that a row is added to the query design grid named Crosstab.
 * 8) Click in the Crosstab row cell under the ShipCountry column, click the arrow that appears, and then click Row Heading.
 * 9) Click in the Crosstab row cell under the ShipCity column, click the arrow that appears, and then click Row Heading.
 * 10) Click in the Crosstab row cell under the ShipVia column, click the arrow that appears, and then click Column Heading.
 * 11) 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.
 * 12) Click in the Total row cell under the Freight column, click the arrow that appears, and then click Sum.
 * 13) On the Query menu, click Run to view the results of the crosstab query.

back to the top

