How To Create Pivot Table In Ms Access And Resolve It’s Related Issue

Summary:

This particular blog emphasizes on the complete information regarding MS Access PivotTable. It also explains the steps required to create pivot table and 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 doesn’t actually change the spreadsheet or database itself.

You can use 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.

Apart from this you can also make use of the 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 the query in PivotTable view

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

  1. Go to the Navigation Pane and make double-tap to the query.
  2. Now on the Home tab, in the Views group, tap to the View option, and then hit the PivotTable View.

This will display the blank PivotTable view without any fields or data.

Step 3: Add Data Fields To The PivotTable View

Now add the fields, which makeup the row and columns headings of PivotTable view. Also enter detail and filter fields. For this, the Field List must be visible.

  • If in case the field list is not appearing then go to the Designtab, in the Show/Hide group, and hit the Field List option.

Add row fields

Row fields are one that makeup the rows of the PivotTable.

Steps to add a row field:

  • From the field list drag down the field list to the area marked Drop Row Fields Here in the PivotTable.

Apart from that, you can add the filter field by using the following procedure:

  1. Make selection for the fields in Field List.
  2. In bottom of the field list, just select the Row Areafrom the drop-down list, and then tap to the Add to option.

Add column fields

To add a column field:

  • Drag the field from the Field List to the area marked Drop Column Fields Herein the PivotTable.

Alternatively, you can add the filter field by using the following procedure:

  1. Select the field in the Field List.
  2. At the end of the Field List, choose Column Area from the drop-down list, and after then hit the Add to option.

Step 4: Change Field Captions And Format Data

  1. Now in the pivot table, just select the label of field which you want to change.
  2. In the Tools group, on the Design tab, tap to the Properties
  3. In Properties dialog box, hit the Captions
  4. In the Caption box, type the caption you want, and then press ENTER.
  5. You can also set other formatting options which you want for the caption.
  6. Go to the Properties dialog box, and tap to the Format
  7. Make use of the command on format tab to sort data and also to format the text and cells.
  8. If your PivotTable contains total rows, click Hide Details on the Design tab to hide the detail fields and show only the total fields.
  9. Repeat steps 3 through 7 to set captions and formats for the total fields.

Step 5: Filter, Sort, And Group Data

  • To filter down the data, hit the down-arrow which is just next to any field name. After then select or the check boxes to get filtering that you want.
  • To group data:
    1. In the PivotTable, tap to the values that you want to group.
    2. After then make right-click one of the selected values, and then tap to the Group Items option. 

After the addition of the grouping level, just select the group and then use the Expand Field and Collapse Field commands on design tab to view or hide individual fields in group, respectively

What Issues Encountered While Working With The Access Pivot Table?

While working with the 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

The issue of PivotTable and Chart controls don’t work can encounter 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

  1. First of all open your Access Database 2007.
  2. 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 Createtab, 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.
  3. In the Visual Basic Editor, go to the Tools menu, and tap to the References.
  4. 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

  1. First of all search for the Microsoft Download Center for exeor owc11.exe, depending on the version that you have find through above mentioned reference missing section.
  2. 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.

  1. 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 learnt very well, how to create pivot table 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.

How To Create Pivot Table In MS Access And Resolve It’s Related Issue