How to Create a Crosstab Query From Scratch in Microsoft Access

Crosstab queries compress the query results and display them in a spreadsheet-like fashion. These instructions apply to Access 97.

Difficulty: Moderately challenging

Instructions

Things You'll Need:

• Microsoft Access

1. From the database window, click on the Query tab.

2. Click on New. A New Query dialog box opens.

3. Click on Design View, then click on OK. The Show Table dialog box appears.

4. Click on the tab that lists the tables or queries that you want to use.

5. Click the name of each object (tables or queries) that you want to add to the query. Click Close.

6. Add fields to the Field row. Specify the query criteria (how you want to separate out information from the selected fields, such as all records with a value greater than 5).

7. Click Query Type on the toolbar. Click on the Crosstab icon.

8. Identify the fields you want to use for the rows. Click the Crosstab row and then click Row Heading.

9. To define the column headings, click the Crosstab column, then click Column Heading.

10. Identify the field values that you want to use in the crosstablulation by clicking on the Crosstab row and then on Value.

11. Identify the type of aggregate function you want to use (such as Sum, Avg, Count, etc.).

12. Identify other criteria as desired.

13. Click View to see the query results.

Tips & Warnings

• You must leave Group By in the Total row in both steps 8 and 9.
• You can also create crosstab queries by using a wizard; the wizard asks you what you want to do with the query, then creates it for you. See the related eHow, at left, for instructions.