How to create a crosstab query in Access 2002

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.

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.

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.

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: For additional information about creating select queries, click the following article number to view the article in the Microsoft Knowledge Base: For additional information about creating parameter queries, click the following article number to view the article in the Microsoft Knowledge Base: 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: For additional information about modifying query properties, click the following article number to view the article in the Microsoft Knowledge Base: