How To Create A Pivot Table In Access 2013

Summary:

This particular blog emphasizes on the complete information regarding MS Access PivotTable.

It also explains the steps required to create a Pivot table in Access.

Along with the fixes to resolve associated issues encountered meanwhile using this Access PivotTable feature.

Fix Microsoft Access Issues
Run a database Scan with Stellar Access Database Repair Tool to repair inconsistent Access database. After the scan is complete, the repair process will restore the database to its normal state fixing all the errors.

By clicking the button above and installing Stellar Repair for Access (14.8 MB, $79 USD), I acknowledge that I have read and agree to the End User License Agreement and Privacy Policy of this site.

What is MS Access PivotTable?

In MS Access, the Pivot table is a programming tool that provides you the option to recognize and summarize selected columns and row of data in a spreadsheet or database table to get the desired report. The Pivot table in Access doesn't actually change the spreadsheet or database itself.

You can use the Access pivot table to make a list of unique values because it summarizes data that can be used to find unique value in a field. It's a good way to take a quick overview of all the values appearing in the field and other inconsistencies.

Have a look over the Access pivot table screenshot:

What is MS Access PivotTable

Apart from this, you can also make use of the Access PivotTable and PivotChart views to perform interactive data analysis or create dynamic, interactive charts.

How To Create a PivotTable View In Access?

Create a PivotTable View In Access

In order to make a PivotTable or PivotChart views for Access queries, form, or tables. Here is how to create a PivotTable view and PivotChart view for an Access Query.

Step 1: Create a query

If you are not having a query to use for PivotTable view, then create a new one. To know how this is to be done you can check out the post regarding Access Query Designing.

Step 2: Open up your query in the PivotTable view

Once the query has been created, try out the following steps to open the query in PivotTable view.

Go to the Navigation Pane and make a double-tap to the query.
Now from the Hometab, go into the Views group and tap the View option. After then hit the PivotTable View.

This will open the PivotTable view without having any data or fields.

Step 3: Within PivotTable View Add Data Fields

Now add some fields, which make up the columns and row headings of PivotTable view. Also, enter the detail & filter fields. For this, it's important to have clear visibility of the Field List.

If in case, field list not appearing clear to you then go to the Design tab and choose the Show/Hide group after then hit on the Field List option.

Add column fields:

To add a column field:

At first drag the field present in the Field List in the PivotTable area which is marked by Drop Column Fields Here.

Or else, you can just add a filter field through the following steps:

Choose a field from Field List.
From the bottom section of the Field List, choose the Column Area option from the drop-down list. After then hit the Add to option.

Add row fields:

Row fields are one that makes up the rows of the Pivot Table in Access.

Steps to add a row field:

At first drag the field present in the Field List in the PivotTable area which is marked by Drop Row Fields Here.

Apart from that, you can just add a filter field through the following steps:

Make a selection for the fields from Field List.
From the bottom section of the Field List, choose Row Area option from the drop-down list. After then hit the Add to option.

Step 4: Make Changes In Field Captions & Format Data

Now in the pivot table, just choose the label for the field which you need to change.
From the Design tab, get into the Tools group. After then hit on the Properties option.
In the dialog box of Properties, hit on the Captions option.
Within the opened Captionbox, just write the caption which you want. After then press the ENTER button.
You can also apply different formatting options for your caption.
now in the same Properties dialog box, you need to tap the Format tab.
Make use of the command on the format tab for sorting up the data. Also to format cells and text.
If your PivotTable is having total rows then hit on the Hide Detailsoption from the Design tab. This will hide all detail fields and only display the total fields.
Repeat steps number 3 to 7 for setting up the format and captions for complete fields.

Step 5: Filter, Sort, & Group Data

For filtering up the data, hit the down arrow present next to the field name. After then either choose or put a checkmark over the filtering option that you require.
For grouping up the data:
1. In your PivotTable, tap on the values which you require for grouping.
2. After then make a right-click on any one of the chosen values. After then tap on the Group Items option.

After the addition of the grouping level, you need to choose the group. And then make use of the Collapse Field and Expand Field commands from the design tab to hide or view each field in a group.

What Issues Encountered While Working With The Access Pivot Table?

While working with the Access pivot table most commonly encountered issues with which users usually stuck off, are :

Access Pivot Table View Missing or
PivotTable and Chart controls don't work

Symptoms

Well, the "PivotTable and Chart controls don't work" error can be encountered on any MS Access 2003/2007/2010 application. At that time it throws the following error message.

Your Microsoft Office Access database or project contains a missing or broken reference to the file '0WC10.DLL' version 1.0.
Your Microsoft Office Access database or project contains a missing or broken reference to the file '0WC11.DLL' version 1.0.

Even After the appearance of such an error, you can open your Access 2007 database file but any of the PivotTable controls or Microsoft Office Chart controls contained on the form, report, or data access page don't work. Along with that the Access display message "There is no object in this control" each time when you try to open an object which contains a PivotTable control or Chart control.

Resolution To Fix MS Access PivotTable Controls Not Working Issue

In order to make an Access 2007 application to show your previous version PivotTable controls and PivotChart controls correctly. You need to download and install the right .dll file first. Make use of the following procedure to find out the missing reference file and then download and install those files.

Determine which reference is missing

First of all, open your Access Database 2007.
Perform the following steps to open the Visual Basic Editor:
- Now open an existing module by double-clicking it in the Navigation Pane.
- On theCreate tab, in theOther group, tap theModule option. If you are unable to find this command then tap to the arrow beneath either theMacro or theClass Module button, and then hit theModule option.
In the Visual Basic Editor, go to theToolsmenu, and tap the References.
In the reference dialog box, within the Available References, search for the references which are marked asMISSING.
- If version 10.0 or version 11.0 of the MS Office Web Component is missing, follow the steps to download and install the required .dll file.

Steps to Download and install the required file

First of all, search for the Microsoft Download Center for exe orowc11.exe, depending on the version that you have to find through the above-mentioned reference missing section.
After then follow the instructions as guided in the Download Center to download and install the file properly.

Note: either you can save the .exe file and then run it to install .dll file or you can make a tap to Run option to install the .dll file directly from the Download Center.

After complete installation. Just make a tap to the Close and then reopen your Access database application to make sure that the Access database controls were working as expected.

Wrap Up:

Hopefully, now you must have learned very well, how to create PivotTable in Access. And, also how to resolve MS Access PivotTable Controls Not Working Issue. So, just try them, and don't forget to share your experiences after trying.

tip Still having issues? Fix them with Stellar Repair for Access:

This software ensures seamless repair & recovery of ACCDB & MDB database and restores all objects including tables, reports, queries, records, forms, and indexes along with modules, macros, etc. Fix Microsoft Access issues now in 3 easy steps:

Download Stellar Access Database Repair Tool rated Great on Cnet.
Click Browse and Search option to locate corrupt Access database.
Click Repair button to repair & preview the database objects.

Pearson Willey is a website content writer and long-form content planner. Besides this, he is also an avid reader. Thus he knows very well how to write an engaging content for readers. Writing is like a growing edge for him. He loves exploring his knowledge on MS Access & sharing tech blogs.