HOW TO: Create a Crosstab Query in Microsoft Access 2000 (304348)


The information in this article applies to:

  • Microsoft Access 2000
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

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


REFERENCES

For more information about creating crosstab 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.

You can also view this information of the World Wide Web by clicking the "Working with Queries" topic on the Microsoft Access 2000 Help page at the following Microsoft Web site:

Microsoft Access 2000 Help

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:

209143 ACC2000: 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:

304361 ACC2000: 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:

304353 ACC2000: 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:

304355 ACC2000: 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:

304357 ACC2000: How to Modify Query Properties in Microsoft Access

To download a sample Microsoft Access database that contains over 20 query examples, including the techniques described in these articles, see the following article in the Microsoft Knowledge Base:

207626 Access 2000 Sample Queries Available in Download Center



back to the top








Modification Type:MinorLast Reviewed:8/9/2004
Keywords:kbdownload kbhowto kbHOWTOmaster KB304348 kbAudITPro
©2004 Microsoft Corporation. All rights reserved.