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.
Other People Are Reading
Instructions
-
-
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.
-
1
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.
