MS Access Pivot Table is an interactive table having summary of large amount of data in a short and tabular format for quick analysis and reporting. This pivot table is capable of sorting, counting, and summing the data which are present in the multiple excel programs. With the help of excel sheets you can easily make pivot tables by dropping and dragging your required information into the relevant boxes. You have also the option to sort and filter your data to know the trends and patterns.

A pivot table groups your data jointly in dissimilar manners, so, that you can draw useful conclusions from it. The “pivot” part of the pivot table allows you to pivot or rotate the data available in the table. This helps you to view the table from different angles. You don’t add, subtract or change your data while creating a pivot. You simply organize the data for revealing beneficial information out of it.

After seeing the benefits of pivot table, I have decided to discuss with you how to create pivot table in Access database through this blog. Please go through the entire blog and know the steps to make MS Access Pivot Table.

How To Create Pivot Table In Access?

Here are the few different methods to create pivot table for Access queries, tables and forms.

Method 1: Creation Of A Query

• On the tab of Create, which is present in the Queries group, hit the option ‘Quick Wizard’ if you wish to create your query or press ‘Query Design’ in case you desire to make the query yourself.ac

While adding fields to the grid of query, be confirm to include the following:

• Fields that include the data you want to initialize or summarize (like numeric data or currency amounts)
• Fields with which you wish to compare data like region, employee, or date etc.

For counting the items in your pivot Table, you must provide a separate ID field for the items that are to be counted. For instant, for counting the number of invoices, your query should have ID of the invoice table.

YOU MAY ALSO LIKE:

7 Ways To Use MS Access DLookup( ) Function That You Can’t Miss…!

Method 2: Opening The Query In The Pivot Table View:

• In case is not open, in the Navigation Pane, then, press double click on the query option.
• Go to the Home tab which there in the Views group, press View, and then hit the option Pivot Table View.

MS Access will display an empty Pivot Table view having no fields or data.

Method 3: Adding Of Data Fields To The Pivot Table View

In this method, the fields are added that create the rows and columns headings of the Pivot Table view, along with the filter and detail fields. To make this happen, the Field List must be visible.

• In case the Field List is invisible, go to the Design tab, and in the option of Show/Hide group, hit Field List.

For Adding Row Fields

Row fields create the rows of the Pivot Table.

Procedure For Adding A Row Field:

• You are required to drag the field from the Field List to area marked as Drop Row Fields in the PivotTable.

Alternative procedure for adding the filter field:

• Choose the field in the Field List.
• At the end of the Field List, choose the Row Area from the list of drop-down, and press Add to.

For Adding Column fields

As the name suggests, column fields create the columns of the Pivot Table.

Procedure for adding a column field:

You should drag the field from the Field List to the area marked as ‘Drop Column Here’ in the Access Pivot Table.

Alternative ways of adding the filter field:

• Choose the field in the Field List.
• At the end of the Field List, choose the Column Area from the drop-down list, and hit Add to.

For Adding The Details Fields

Detail fields create the detail cells of the Pivot Table.

Procedure For Adding Detail Field:

You must drag the field from the Field List to the area marked as’ Drop Totals or Detail Field Here’ in the Access Pivot Table.

Alternative method to add the filter field:

• First of all choose the field in the Field List.
• At the end of the Field List, choose the Detail Area from the drop-down list, then, after that press Add to.

For Adding The Filter Fields

Filter fields are of optional nature. After adding a filter field, you are allowed to filter whole Pivot Table through a value.

ALSO READ:

How To Convert/Export/Import Access Database To CSV

Procedure For Adding The Filter Field:

Please drag the field from the Field List to the area marked as Drop Filter Here.

Alternative procedure to add the filter field:

• Choose the field in the Field List.
• At the end of the Field List, choose the Filter Area from the drop-down list, and then, press the option Add to.

For Moving A Field

Drag the name of the field to the desired location. Be confirmed to drag the field name itself and not one of the field values.

For Deleting A Field

Choose the name of the field which you wish to delete, and then go to the Design tab and in the Active Field group, press Remove Field.

Note:  The above action eliminates the field from the view, but the field is still there in the data source.

Conclusion

The MS Access Pivot Table is really a beneficial tool. It has a lot of benefits for the users. But the important thing is to create pivot table in Access database. In this blog I have discussed about the pivot table and its importance. I have also discussed the a few methods to create pivot table in Access Database. Hope that it turns out beneficial for you.