Create a crosstab query without using a wizard

To see an example of a crosstab query before you begin, click .

1. In the Database window, click Queries under Objects, and then click New on the Database window toolbar.
   
   
2. In the New Query dialog box, click Design View, and then click OK.
   
   
3. In the Show Table dialog box, click the tab that lists the tables or queries whose data you want to work with.
   
   
4. Double-click the name of each object you want to add to the query, and then click Close.
   
   
5. Add fields to the Field row in the design grid and specify criteria.
   
   
6. On the toolbar, click Query Type , and then click Crosstab.
   
   
7. For the field or fields whose values you want to appear as row headings, click the Crosstab row, and then click Row Heading.

   You must leave the default Group By in the Total row for these fields.

8. For the field whose values you want to appear as column headings, click the Crosstab row, and then click Column Heading. You can choose Column Heading for one field only, and you must leave Group By in the Total row for this field.

   By default, the column headings are sorted in alphabetic or numeric order. If you want them to appear in a different order, or if you want to limit which column headings display, set the query's ColumnHeadings property.

   How?

9. For the field whose values you want to use in the cross-tabulation, click the Crosstab row, and then click Value.

   Only one field can be set to Value.

10. In the Total row for this field, click the type of aggregate function you want for the cross-tabulation (such as Sum, Avg, or Count).
    
    
11. To specify criteria that limit row headings before the calculation is performed, enter an expression in the Criteria row for a field with Row Heading in the Crosstab cell. For example, you might display sales totals for products in certain categories, such as meat and seafood.

    To specify criteria that limit records before the row headings are grouped and before the cross-tabulation is performed, add the field you want to set criteria for to the design grid, click Where in the Total cell, leave the Crosstab cell blank, and then enter an expression in the Criteria row. (The query results won't display fields that have Where in the Total row.) For examples of how criteria affect calculations in queries, click .

12. To view the query's results, click View on the toolbar.

    To stop the query after you start it, press CTRL+BREAK.

Notes

• The query Quarterly Orders by Product in the Northwind sample database is an example of a crosstab query. To view this query, open the Northwind database in your Office folder's Samples folder, and then open Quarterly Orders by Product in Design view. For information on opening Northwind, click .
  
  
• If you include a field in the query design grid, but click the (Not Shown) option in the Crosstab cell and Group By in the Total cell, Microsoft Access groups on it as a Row Heading, but doesn't display the row in the query's results.
  
  
• The values in your Column Heading field may include characters usually not allowed in field names, such as decimals. If they do, Microsoft Access replaces the character with an underscore in the datasheet.
  
  
• For information on troubleshooting your crosstab query, click .
  
  
• For information on creating a PivotTable list in a data access page, click .