ACC2002: How to Create a Pareto Chart That Uses a PivotChart View (304180)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q304180
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

SUMMARY

With a Pareto chart, you can easily analyze categorical data. In a Pareto chart, categories are arranged in descending order from left to right as histogram columns, based upon a selected numeric criterion, from highest to lowest values. Then, an ascending plot line for cumulative percentage is overlaid on the histogram chart. In this way, you can quickly determine which small number of categories makes up a significant percentage of the total value.

This article shows you how to create a basic Pareto chart that uses a PivotChart view that is based on the Sales By Category query in the sample database Northwind.mdb.

MORE INFORMATION

Creating a Pareto Chart

To make a Pareto chart in Microsoft Access, follow these steps.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

Create the Data Source for the Chart:

  1. Start Microsoft Access, and then open the sample database Northwind.mdb.
  2. In the Database window, click Queries under Objects, and then click Sales By Category.
  3. On the Edit menu, click Copy.
  4. On the Edit menu, click Paste.
  5. Save the copy of the Sales By Category query as qrySalesByCategory.
  6. Open the qrySalesByCategory query in Design view. Delete the CategoryID field and the ProductName field from the query design grid. Set the Sort order of the ProductSales field to Descending.
  7. On the Query menu, click Make Table to change the query to a make-table query. In the Make Table dialog box, type tblSalesByCategory in the Table Name box.
  8. Save the query, and then run it. Accept the message to paste rows to a new table.
  9. Close the query.
  10. In the Database window, click Queries under objects, and then click New to create a new query.
  11. In the New Query dialog box, click Design View, and then click OK.
  12. In the Show Table dialog box, click tblSalesByCategory, click Add, and then click Close.
  13. Drag the CategoryName field to the query design grid.
  14. In the next column on the query design grid, type the following in the Field row:

    ProductSalesTotal: [ProductSales]

  15. In the next column on the query design grid, type the following in the Field row:

    NOTE: In the following example, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when you re-create this example.

    CumPct: DSum("[ProductSales]","tblSalesByCategory", _
    "[ProductSales]>=" & [ProductSalesTotal] _
    & "")/DSum("[ProductSales]","tblSalesByCategory")

  16. Save the query as qrySalesByCategoryPareto, and then close it.

Create a Chart Form:

  1. In the Database window, click Forms under Objects, and then click New to create a new form.
  2. In the New Form dialog box, click AutoForm: PivotChart, click qrySalesByCategoryPareto in the Choose the table or query where the object's data comes from box, and then click OK.
  3. Drag Category Name from the Chart field list to the Drop Category Fields Here area.
  4. Drag ProductSalesTotal from the Chart field list to the Drop Data Fields Here area.
  5. Drag CumPct from the Chart field list to the Drop Data Fields Here area.
  6. Close the Chart field list.

Modify the Chart Design:

  1. Right-click in the white space away from the chart, and then click Properties.
  2. On the General tab of the Properties dialog box, set the Select box to Sum of CumPct.
  3. On the Type tab of the Properties box, click Line as the type of chart, and then click the first picture in the right pane.
  4. Click the General tab, and then set the Select box to Chart Workspace.
  5. Click the Series Groups tab.
  6. In the Select One or More Series box, click 2 Sum of CumPct, make sure that New Group is selected in the Operation box, and then click OK.
  7. In the Add Axis box, click 2, click Right in the Axis position box, and then click Add.
  8. On the General Tab, set the Select box to Value Axis 2.
  9. On the Format tab, click Percent in the Number list.
  10. Close the Properties dialog box.
  11. Right-click inside the first column of data, point to Sort, and then click Sort Descending.
  12. Save the form.
You now have a Pareto chart that shows the ordered product sales categories and their corresponding percentages.
Modification Type:MajorLast Reviewed:9/27/2003
Keywords:kbhowto KB304180
©2003 Microsoft Corporation. All rights reserved.