ACC2000: How to Create a Pareto Chart Using Microsoft Graph

A Pareto chart provides a simple way of analyzing categorical data. In a Pareto chart, categories are arranged in descending order from left to right as histogram columns, based upon a selected numerical 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 make up a significant percentage of the total value.

This article shows how to create a basic Pareto chart, using the Sales By Category query in the sample database Northwind.mdb.

Creating a Pareto Chart

To make a Pareto chart in Microsoft Access 2000, follow these sets of 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 open the sample database Northwind.mdb.
2. In the Database window, click Queries, and then select Sales By Category. On the Edit menu, click Copy, and then click Paste. Save the copy of the Sales By Category table as qrySalesByCategory.
3. Open the qrySalesByCategory query in Design view. Delete the CategoryID field and the ProductName field from the QBE grid. Set the Sort order of the ProductSales field to Descending.
4. 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.
5. Save the query and run it. Accept the message to paste rows to a new table.
6. Close the query.
7. In the Database window, click Queries, and then click New to create a new query. In the New Query dialog box, select Design View, and then click OK. In the Show Table dialog box, click tblSalesByCategory, click Add, and then click Close.
8. Drag the CategoryName field to the QBE grid.
9. In the next column on the QBE grid, type the following in the Field row:
   ProductSalesTotal: [ProductSales]
10. In the next column on the QBE 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 re-creating this example.
    CumPct: DSum("[ProductSales]","tblSalesByCategory", _
    "[ProductSales]>=" & [ProductSalesTotal] _
    & "")/DSum("[ProductSales]","tblSalesByCategory")
11. Save the query as qrySalesByCategoryPareto, and then close it.

Create a Chart Form

1. In the Database window, click Forms, and then click New to create a new form. In the New Form dialog box, click Chart Wizard, select qrySalesByCategoryPareto as the query where the object's data comes from, and then click OK.
2. Move all three available fields to the Fields for Chart box. Click Next.
3. Select Line Chart as the type of chart to use. Click Next.
4. Double-click SumofProductSalesTotal, and then click None under Summarize.
5. Click the CumPct field, and drag it underneath the ProductSalesTotal field. (Do not replace ProductSalesTotal, but add CumPct to the Data section.)
6. Double-click the SumofCumPct field, and then select None under Summarize. Click Next.
7. For the chart title, type Sales by Category - Pareto, and then click Finish.

Modify the Chart Design

1. On the View menu, click Design View.
2. On the View menu, click Properties to display the form property sheet, if it is not already visible.
3. On the Form property sheet, set the Width to 8".
4. Click the Detail section, and then set the Height to 6".
5. Click the Unbound Object Frame that contains the chart, and then set Height to 6" and Width to 8".
6. Right-click the chart, point to Chart Object, and then click Edit.
7. Right-click the vertical axis, and then select Format Axis. Click the Number tab. Select the Currency category. Click the Font tab, click to clear the Auto scale box, and then click OK.
8. Right-click the horizontal axis, and then select Format Axis. (You may need to click slightly below the axis to avoid selecting the data series).
9. Click the Alignment tab, and then set the label alignment to automatic. Click the Font tab, click to clear the Auto scale check box, and then click OK.
10. Right-click the ProductSalesTotal data series. Select Chart Type, click Column, and then click OK.
11. Right-click the CumPct data series. Select Format Data Series.
12. Click the Axis tab. Under Plot series on, click Secondary Axis, and then click OK.
13. Right-click the new vertical axis that now appears on the right side of the chart. Select Format Axis. Click the Number tab. Select the Percentage category. Click the Font tab, click to clear the Auto scale check box, and then click OK.
14. Resize the chart with the mouse pointer to fill the form. Resize and move the legend to an appropriate place on the chart, if necessary.
15. Save the form. On the View menu, click Form View. You should now have a Pareto chart showing the ordered product sales categories and their corresponding percentages.

For additional information about working with a chart, click the article numbers below to view the articles in the Microsoft Knowledge Base: