Query Pivot Table

A cross-tab query summarizes data in a spreadsheet format. A cross-tab query displays data in the columns and rows. You specify fields for columns, rows, and cells.

1. From the Tools menu, select the Queries... option.

   The Queries form is displayed.
   
   

2. Select the module and record area, and either create a new query, or modify an existing query.

   Information on creating or modifying a query is available in Create New Query and Modify Query.

3. Verify that Report is selected for Query Type(s) for the query.

4. Click the Pivot Table tab.

   The fields listed in Available Cross-Tab Fields are the selected fields from the Fields section.

   • Properties

   • Tables

   • Fields

   • Join

   • Filter

   • Order By

   • Group By

   • Having

   • Pivot Table

5. Highlight a field and press Column.

   A column for each unique value in the specified field is created when the query is run.

6. Highlight a field and press Row.

   A row for each unique value in the specified field is created when the query is run.

7. Highlight a field and press Cell.

   A value will be entered in the intersecting column/row position.

8. Perform cross-tab analysis.

   These options let you control the values displayed in the cross-tab table.

   1. Check Add a subtotal column to each row containing... to add a column at the end of the table.

   2. Select one of the three options for a subtotal column.

      To do this	Choose this
      Total the number of cells with values in each row.	Number of cells in row containing data
      Tally the values in each row.	Sum of cell values in a row
      Display the value percentage for each row.	Sum of cell values in a row as a percent of a grand total

9. Perform cell field analysis.

   These options define the appearance of the cell value.

   To do this	Choose this
   Display the highlighted cell value.	None
   Display the number of cell values.	Count
   Tally all the values in each cell and displays the total.	Sum
   Display the average value for each cell.	Average
   Display the largest value for each cell.	Maximum
   Display the smallest value for each cell.	Minimum
   Only count different values.	Count distinct values only

10. Save the changes you have made.

    The query will be displayed in a cross-tabulation spreadsheet.

    Information on cross-tabulation is available in Cross Tab Report Note.