ACC: How to Create a Pareto Chart Using MS Graph

ID: Q154072

The information in this article applies to:

Microsoft Access versions 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

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 view the top percentage categories. This article shows how to create a basic Pareto chart. This example uses the Sales By Category query in the sample database Northwind.mdb.

MORE INFORMATION

Method for Making a Pareto Chart

NOTE: In Microsoft Access 2.0, field names in existing tables contain spaces to form two distinct words. For example, the ProductName field is spelled Product Name in version 2.0. If you are following this example using Microsoft Access 2.0, please make the corresponding adjustments.

Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0).

Create the data source for the chart as follows:
1. Copy and paste the Sales By Category query to a new query. Name this new query qrySalesbyCategory.
2. Open the qrySalesbyCategory query in Design view. Delete the CategoryID field and the ProductName field. (In Microsoft Access 2.0, delete the Product Name field.) Set the Sort order of the ProductSales field to Descending.
3. Change the query to a Make Table query. In the Make Table dialog box, type tblSalesbyCategory in the Table Name box.
4. Save the query and run it. Accept the message to paste rows to a new table.
5. Close the query.
6. Create a new query. Add the tblSalesbyCategory table to the query. Drag the CategoryName field to the QBE grid.
7. Make a calculated field to the right of CategoryName. Name it ProductSalesTotal. Set it equal to the ProductSales field from the tblSalesbyCategory table:
```
          ProductSalesTotal: [ProductSales] 
```
8. Make another calculated field to the right of ProductSalesTotal. Name it CumPct. Set this field equal to the following.
  
  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.
```
          DSum("[ProductSales]","tblSalesbyCategory","[ProductSales]>=" _
          & [ProductSalesTotal] _
          & "")/DSum("[ProductSales]","tblSalesbyCategory") 
```
9. Save the query and name it qrySalesbyCategoryPareto. Close the query.

Create a Chart Form as follows:
1. Create a new form using the Chart Wizard based on the query qrySalesbyCategoryPareto. (In Microsoft Access 2.0, in the Select a Table/Query box, select the qrySalesbyCategoryPareto query. Click the Form Wizards button. In the Form Wizards box, select Graph, and then click OK.)
2. Move all three available fields to the Fields For Chart box. Click Next. (In Microsoft Access 2.0, move all three available fields to to the Fields For Chart box. Click Next. Select Category Name as the field to be on the horizontal axis, and click Next. Select both remaining fields to appear on the legend. Accept the default totaling method of Add(Sum) the numbers, and click Next.)
3. Select the Line Chart as the type of chart to use. Click Next.
4. Double-click SumofProductSalesTotal, and select None for Summarize. (In Microsoft Access 2.0, skip this step.)
5. Click the CumPct field, and drag it underneath the ProductSalesTotal field. (Do not replace ProductSalesTotal, but add CumPct to the Data section.) (In Microsoft Access 2.0, skip this step.)
6. Double-click the SumofCumPct field, and select None for Summarize. Click Next. (In Microsoft Access 2.0, skip this step.)
7. For the chart title, type Sales by Category - Pareto, and then click Finish. (In Microsoft Access 2.0, type the chart title Sales by Category - Pareto. Click Modify the design of the form or the graph, and then click Finish.)

Update the form's design view with the graph's actual data:
1. Set the graph's Enabled property to Yes and Locked property to No.
2. Close and save the form.
3. Open the form in Form view.
4. Click the Graph object so it has the focus.
5. On the Edit menu, point to Chart Object and then click Open to start Microsoft Graph. Note that Microsoft Graph displays the actual data.
6. On the File menu, click Update.
7. On the File menu, click Exit & Return to <FormName>.
8. Close the form.

Modify the Chart Design as follows:
1. On the View menu, click Form Design. (In Microsoft Access 2.0, the form will already be open in Design view.)
2. Using the right mouse button (right-click), click the chart in Form design, click Chart Object, and then click Edit.
3. Right-click the vertical axis, and select Format Axis. Click the Number tab. Select the Currency category, and then click OK.
4. Right-click the horizontal axis, and select Format Axis. (You may need to click slightly below the axis to avoid selecting the data series).
5. Click the Alignment tab, and choose the label alignment to be automatic. Click OK. (Choose vertical rather than horizontal in earlier versions. Click OK.)
6. Right-click the ProductSalesTotal data series. Select Chart Type, select Column, and then click OK.
7. Right-click the CumPct data series. Select Format Data Series.
8. Click the Axis tab. For "Plot Series on...," click Secondary Axis, and then click OK.
9. Right-click the new vertical axis that is on the right side of the chart. Select Format Axis. Click the Number tab. Select the Percentage category, and then click OK.
10. Resize the chart with the mouse pointer to make more room for the legend. Resize and move the legend to an appropriate place on the chart.
11. On the File menu. click Update. On the File menu, click Exit & Return.
12. Size the chart control in the form with your mouse pointer to give it a reasonable size.
13. Save the form as frmPareto. View the chart in Form view.

This will give you a Pareto chart showing the ordered product sales categories and their corresponding percentages.

REFERENCES

For more information about working with a chart, please see the following articles in the Microsoft Knowledge Base:

Q141235 ACC: How to Change the Order of Columns in a Chart (95/97)

Q147193 ACC: How to Format a Label for Each Axis of a Graph

Q154454 ACC: Microsoft Graph Always Shows Sample Data in Design View

Keywords : kbusage FmsHowto
Version : 2.0 7.0 97
Platform : WINDOWS
Issue type : kbhowto