MS Access for the Business Environment: Create a PivotTable View in Access

About the Series ...

This article continues the series, MS Access for the Business Environment. The primary focus of this series is an examination of business uses for the MS Access relational database management system. The series is designed to provide guidance in the practical application of data and database concepts to meet specific needs in the business world. While the majority of the procedures I demonstrate will be undertaken with Access 2002, many of the concepts that we expose in the series will apply to numerous versions of MS Access.

For more information on the series, as well as the hardware / software requirements to prepare for the tutorials we will undertake, please see Tutorial 1: Create a Calculated Field with the Expression Builder.

Introduction to this Tutorial

This tutorial will introduce the PivotTable in MS Access. Among numerous enhancements that appear in Access 2002, one of the most exciting is the addition of PivotTable and PivotChart views for the tables and queries of the database. PivotTables and PivotCharts provide a means of interactive data analysis, thus broadening our business intelligence toolset in Access; both allow us to organize and summarize information into useful analysis presentations called views. We explore the PivotTable view in this lesson, and then expose the PivotChart view in Lesson 3: Create a PivotChart View in Access.

In this tutorial, we will focus upon the creation and use of PivotTable views in Access. This lesson will include:

• Creation of a basic query upon which to base a PivotTable view.
• A brief introduction to PivotTable Views, including navigation and features;
• A practical walkthrough of the process of creating of a PivotTable view in an Access database.
• An exploration of steps to take in modifying a PivotTable View
• A brief discussion of formatting features.

Let's begin by discussing the features of the PivotTable view. Then, having an idea of the "end purpose," we will look at the steps required to create a PivotTable view to meet a hypothetical business need.