js editor
Web development
This wizard creates a cross-tab query for summarizing table data in a spreadsheet format.
-
From the Tools menu, choose Wizards, and click Query.
-
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 Working with Views.
-
Use the Databases and Tables controls to locate and select the table you want to use.
-
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.
-
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
-
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
-
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.
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 DisplayNull 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.