Add a pivot chart to your Access reports

Pivot charts can add value to your reports. In this tip, Mary Ann Richardson shows how to make the most of this useful Access feature.

Pivot charts in Access are not just for display only. You can also distribute your pivot table analysis in a printed report. For example, you can create a pivot chart that compares employees' sales data for 2005 and then add it to the employees' sales report.

To do so, create an Employees_and_Orders query that lists the employees and their total orders by month and year. This query includes four fields: Employee, Order Month, Order Year (2001 through 2006), and Order Total. Now follow these steps:

  1. In the database window under Objects, click Forms.
  2. Click the New button.
  3. Select Autoform: Pivot Chart.
  4. Select the Employees_and_Orders query from the drop-down list and click OK.
  5. Drag the Employee field from the Chart Field List to the Category drop zone.
  6. Drag the Order Year field to the Filter drop zone.
  7. Drag the Order Total field to the Data drop zone.
  8. Click the Order Year button and clear all check boxes except for 2005.
  9. Click the Properties tool in the PivotChart toolbar.
  10. Click the General tab and select Value Axis 1 Title from the drop-down list.
  11. Click the Format tab and enter Total Sales in the Caption text box.
  12. Click the General tab and select Value Axis 2 Title.
  13. Click the Format tab and enter Employee in the Caption text box.
  14. Click the General tab and select Title from the drop-down list.
  15. Click the Format tab and enter 2005 Total Sales Per Employee.
  16. Click the Show/Hide tab and clear the FieldButton/Drop Zones check box.
  17. Press [Ctrl]+. (period) until you reach the Form's Design view and press [F4].
  18. Click in the Default View Property text box and select PivotChart from the drop-down list.
  19. Save the form as 2005 Employee Sales Subform.
  20. Click Reports under Objects in the Database Window.
  21. Select New, click Design View, and click OK.
  22. Drag the 2005 Employee Sales Subform to the report grid.
  23. Delete the subform label and move and resize the control as needed to display the chart within the report.

Miss a tip?

Check out the Microsoft Access archive, and catch up on our most recent Access tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Editor's Picks

Free Newsletters, In your Inbox