This particular blog emphasizes on the complete information regarding MS Access PivotTable. It also explains the steps required to create pivot table in Access. Along with the fixes to resolve associated issues encountered meanwhile using this Access PivotTable feature.
What is MS Access PivotTable?
In MS Access, Pivot table is a program tool which provides you the option to recognize and summarize selected columns and row of data in a spreadsheet or database table to get a desired report. Pivot table in Access doesn’t actually change the spreadsheet or database itself.
You can use Access pivot table to make a list of unique values because it summarize data that can be used to find unique value in a field. It’s a good way to take quick overview over all the values that appearing in the field and other inconsistencies.
Have a look over Access pivot table screenshot:
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
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 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 double-tap to the query.
- Now from the Home tab, go into the Views group and tap to 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 makeup 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 following steps:
- Choose field from Field List.
- From the bottom section of Field List, choose Column Area option from drop-down list. After then hit the Add to option.
Add row fields:
Row fields are one that makeup 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 following steps:
- Make selection for the fields from Field List.
- From the bottom section of Field List, choose Row Area option from 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 Design tab, get into the Tools group. After then hit on the Properties option.
- In the dialog box of Properties, hit on Captions option.
- Within the opened Caption box, 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 to the Format tab.
- Make use of the command on 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 Details option from the Design tab. This will hide all detail fields and only display the total fields.
- Repeat from 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 check mark over the filtering option that you require.
- For grouping up the data:
- In your PivotTable, tap on the values which you requires for grouping.
- After then make right-click on any one of the chosen values. After then tap to 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 fields in 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
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 error, you can open your Access 2007 database file but any of the PivotTable controls or Microsoft Office Chart controls contained on 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 tries 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 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 the Create tab, in the Other group, tap to the Module option. If you are unable to find this command then tap to the arrow beneath either the Macro or the Class Module button, and then hit the Module option.
- In the Visual Basic Editor, go to the Tools menu, and tap to the References.
- In reference dialog box, within the Available References, search for the references which are marked as MISSING.
- 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 or owc11.exe, depending on the version that you have find through 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 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.
Hopefully now you must have learnt 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.