Cross-Tab Wizard

  • Article

This wizard creates a cross-tab query for summarizing table data in a spreadsheet format.

To access the Cross-Tab wizard

  1. From the Tools menu, choose Wizards, and click Query.
  2. In the Wizard Selection dialog box, choose Cross-Tab Wizard.

Step 1 - Select Fields

In this step, you can choose a free table or a table within a database as the source for your pivot table. You can select only fields from a single table or view. For more information, see Creating Views.

To select the fields for your cross-tab query

  1. Use the Databases and Tables controls to locate and select the table you want to use.
  2. In the Available fields window, select three or four fields you want to use from the selected table, and use the arrow buttons to move them to the Selected fields window.

You need to choose at least three fields; one each for row values, column values, and data.

Step 2 - Define Layout

In this step, you can specify which field values will be calculated for the data. For example, if you have an Orders table that contains, among others, a field for city, a field for region, and a field for order amount, you could create a pivot table that would display in the data area, sums for all the cities, by region. At the bottom, the table will display totals for each column. At the far right, the table will display totals for each row.

To define the cross-tab layout

  1. From the Available fields list, drag a field to the Rows box. The cross-tab query will contain a row for each unique value in the field that you drag to the Rows box
  2. From the Available fields list, drag a field to the Columns box. The cross-tab query will contain a column for each unique value in the field that you drag to the Columns box
  3. From the Available fields list, drag a field to the Data box. Because this field will be summarized, generally it is best to drag a numeric field here.

If you have a large table, you might want to first create a view that contains the desired fields and then create a cross-tab query from that view. For more information, see Creating Views.

Step 3 - Add Summary Information

In this step, you can determine if you want to add a subtotal column and summary information in your data. To add summary information, select the appropriate radio button (Sum, Count, Average, Max, or Min) in the Summary section. To add a subtotal column, select the appropriate radio button (Sum of data, Number of cells containing data, or Percentage of the table total) in the Subtotals section. If you do not want to add a subtotal column, select the None radio button in the Subtotals section. The totals will appear in the rightmost column of your cross-tab query results.

Step 4 - Finish

This step makes it possible to save and specify the use of your cross-tab query.

  • Save cross-tab query
    Saves the cross-tab query for use later.
  • Save and run cross-tab query
    Saves the cross-tab query and runs it immediately.
  • Save cross-tab query and modify in the Query Designer
    Saves the cross-tab query and opens it in the Query designer so you can modify it.

You can preview your cross-tab query by clicking the Preview button. To view null values, select the Display Null Values check box.

At any point after you save the cross-tab query, you can open and modify it as you would with any other query in the Query and View designers.

See Also

Wizards Overview | Query Wizard | Query and View Designers | Creating Queries