ACC: How to Create a Pareto Chart Using MS Graph

• 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.

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

2. Create the data source for the chart as follows:

       a. Copy and paste the Sales By Category query to a new query. Name this
          new query qrySalesbyCategory.
   

       b. 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.
   

       c. Change the query to a Make Table query. In the Make Table dialog
          box, type tblSalesbyCategory in the Table Name box.
   

       d. Save the query and run it. Accept the message to paste rows to a new
          table.
   

       e. Close the query.
   

       f. Create a new query. Add the tblSalesbyCategory table to the query.
          Drag the CategoryName field to the QBE grid.
   

       g. 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]
   
       h. 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")
   
       i. Save the query and name it qrySalesbyCategoryPareto. Close the
          query.
   
   

3. Create a Chart Form as follows:

       a. 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.)
   

       b. 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.)
   

       c. Select the Line Chart as the type of chart to use. Click Next.
   

       d. Double-click SumofProductSalesTotal, and select None for Summarize.
         (In Microsoft Access 2.0, skip this step.)
   

       e. 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.)
   

       f. Double-click the SumofCumPct field, and select None for Summarize.
          Click Next. (In Microsoft Access 2.0, skip this step.)
   

       g. For the chart title, type "Sales by Category - Pareto" (without
          quotation marks), 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.)
   

4. Update the form's design view with the graph's actual data:

       a. Set the graph's Enabled property to Yes and Locked property to No.
   

       b. Close and save the form.
   

       c. Open the form in Form view.
   

       d. Click the Graph object so it has the focus.
   

       e. On the Edit menu, point to Chart Object and then click Open to
          start Microsoft Graph. Note that Microsoft Graph displays the actual
          data.
   

       f. On the File menu, click Update.
   

       g. On the File menu, click Exit & Return to <FormName>.
   

       h. Close the form.
   

5. Modify the Chart Design as follows:

       a. On the View menu, click Form Design. (In Microsoft Access 2.0, the
          form will already be open in Design view.)
   

       b. Using the right mouse button (right-click), click the chart in Form
          design, click Chart Object, and then click Edit.
   

       c. Right-click the vertical axis, and select Format Axis. Click the
          Number tab. Select the Currency category, and then click OK.
   

       d. Right-click the horizontal axis, and select Format Axis. (You may
          need to click slightly below the axis to avoid selecting the data
          series).
   

       e. Click the Alignment tab, and choose the label alignment to be
          automatic.  Click OK. (Choose vertical rather than horizontal in
          earlier versions. Click OK.)
   

       f. Right-click the ProductSalesTotal data series. Select Chart Type,
          select Column, and then click OK.
   

       g. Right-click the CumPct data series. Select Format Data Series.
   

       h. Click the Axis tab. For "Plot Series on...," click Secondary Axis,
          and then click OK.
   

       i. 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.
   

       j. 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.
   

       k. On the File menu. click Update. On the File menu, click Exit &
          Return.
   

       l. Size the chart control in the form with your mouse pointer to give
          it a reasonable size.
   

       m. Save the form as frmPareto. View the chart in Form view.
   

REFERENCES

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