Creating and Editing PivotTables

You can create a “PivotTable” which is best compared to a “Crosstab query’, only 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”.

So 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.
For example:
I drag the “Product Name” from the field list to the “Row field drop” zone.
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, this list will unfold or collapse respectively.
I drag the “Month” field to the “Column field drop” zone, the “Categories” field to the “Filter field drop” zone, and the “Amount” to “Detail field drop” zone.

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

What the “Hide Details” or “Show Details” buttons in the “Ribbon” do, is clear.
The “Drill buttons” is a toggle button that shows / hides details with the plus (+) or minus(-) sign next the field, in this case for “Months”.

By clicking on the plus and 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” button OR ” Collapse Field” button 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 button “Show Top/Bottom”, 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”. Therefore 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 zone 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 clicked the “Auto Calc” button, every article of every month and the total is displayed.

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

 

Featured Courses

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