Creating and Editing “PivotTable”

You can create a “PivotTable” which similar to a “Crosstab query’, only that it is much more powerful because it allows us to manipulate detailed information.
For this example, I will use the “Sold goods by category” query in the “Northwind” database that comes when we have purchased “Access”.

To start, I open this query and click the downward-pointing arrow below the “View” button in the “Ribbon”.

I choose the “PivotTable View” in the drop-down menu.
This opens a blank “PivotTable” with a field list and a number of “drop fields”.
These “drop fields” are divided into several sections.
You have the “Filter fields”, “Column fields”, “Row fields” and “Detail fields”.

If you do not see the “Field List” then click the “Drop Zones” button in the “Ribbon”. These buttons are also toggle buttons, so if you do not want to see these fields, click this button again.

To create a “PivotTable”, drag fields from the field list in the drop fields areas.
For example:
I drag the “Product Name” from the field list to the drop “Row field” area.
This places all the products on the left side of the table.
Clicking on the plus or minus sign next to the field in the field list, will unfold or collapse the list respectively.
I drag the “Month” field to the drop “Column field” area, the “Categories” field to the drop “Filter field” area, and the “Amount” to drop “Detail field” area.

Once you drag all the required fields from the field list to the “PivotTable”, you can close this list.

What the “Hide Details” or “Show Details” buttons in the “Ribbon” do, seems clear.
The “Detail Controls” toggle button with the plus (+) or minus(-) sign next the field, in this case “Months”, Hides/ Shows details.

By clicking on the plus signs, minus signs, we can see the data in the “PivotTable” in a more detail or less detail.
The same is accomplished by selecting the field in the “PivotTable” and clicking on the “Expand Field” OR “Collapse Field” buttons in the “Ribbon”.

To remove a field, first select the field name in the “PivotTable” and click the “Delete Field” button.

By clicking on the downward pointing arrow beside the field names, we can filter out the different data from this field.
By clicking on the “Auto Filter” button in the “Ribbon”, we can remove any filters that were applied.
Click on this button again to apply the filter again.

Clicking on the “Show Top / Bottom”, opens a drop-down list that lets you filter the columns or rows, depending on which field was selected in the “PivotTable”.
For example, select the “Product Name”, click the “Show Top/Bottom” button, select “Show only the top ” and select the number of records to show, suppose 2. Then only the top two products from this list will be displayed.

To change the position of the fields in the “PivotTable”, you can click and drag it to the desired “Drop Zone”. That is why we call this a “PivotTable”. The same is accomplished by selecting the field and clicking on the “Move Field” button in the “Ribbon”. Choose the area to where you want to move this field in the drop-down menu.
The “Auto Calc” button in the “Ribbon” allows us to select a function from the drop-down menu, a sum, count etc,. of this column, row or detailed field.
For example, when the “Amount” field is selected and we choose “Sum” in the drop-down menu. When we click the “Auto Calc” button, the total for every article for every month is displayed.

If you click the “Hide Details” button in the “Ribbon”, only the sum per article and per month is displayed.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats