ACC: How to Create a Chart That Does Not Summarize Data

• Microsoft Access versions 2.0, 7.0, 97

SUMMARY

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

When you create a chart in Microsoft Graph, data is automatically summarized for each distinct value on the x axis, which is also known as the independent axis. For example, if you plot a field containing dates along the x axis, and if more than one record contains a particular date in that field, that date appears only once on the x axis. The values in the data field of any records that contain that date are summarized according to the function that you have selected (Sum, Average, or Count, for example); the result is a single value that is represented in the chart. Therefore, the chart displays only a single data point for a particular date, even though there may be multiple records corresponding to that date.

However, you may not want to summarize your data. For example, if you have three records that contain a particular date, you may want your chart to display three distinct data points for that date. If you are using Microsoft Access version 2.0, or if you are plotting date values along the x axis, you must change the SQL statement on which the chart is based to prevent the data from being summarized.

MORE INFORMATION

In Microsoft Access 2.0, the Chart Wizard does not give you an option not to summarize the data.

In Microsoft Access 7.0 and 97, the layout screen of the Chart Wizard allows to you specify how you want to summarize the data. By default, data is summarized by Sum, and the caption of a field button that corresponds to a data field is "SumOf<FieldName>," where <FieldName> is the name of the data field. When you double-click a field button in the Data box, the Summarize dialog box appears, and you can select a function by which to summarize the data.

However, if you select None in the Summarize list and click OK, and if the axis box contains a field whose data type is Date/Time, you receive the following error when you click Next to advance to the next screen of the Chart Wizard:

   All data fields must be summarized (using Sum, Avg, Min, Max or
   Count) when a date filed is used in the Axis or Series. To change the
   way a field is summarized, double-click it.

Create the Data

1. Start Microsoft Access and open any database.

2. Create the following table named tblChartData:

         Table: Table1
         -----------------------
         Field Name: TransDate
            Data Type: Date/Time
         Field Name: TransAmt
            Data Type: Number
   
   

3. Save the table as tblChartData; when prompted to create a primary key, click No.

4. Switch to Datasheet view and enter the following records:

         TransDate    TransAmt
   
         1/1/95           9
         1/1/95           7
         1/5/95           8
         1/5/95           6
   
      Close the table.
   
   

Create the Chart

If you are using Microsoft Access 2.0, follow these steps:

1. In the Database window, click the Form tab, and then click New.

2. In the New Form dialog box, click tblChartData in the Select A Table/Query list; then click the Form Wizards button.

3. In the Form Wizards dialog box, click Graph. Click OK.

4. Move TransDate and TransAmt from the Available Fields list to the list of fields for the graph. Click Next.

5. Click "Only along the graph's axis" to group the data by dates, and then click Next.

6. Click By Day when you are asked how you want to group the data. Click Yes to indicate that you want to use all the data. Then click Next.

7. When asked how you want to calculate the totals for each category, click Add (sum) the numbers, and then click Next.

8. In the chart type screen, click the Line Chart button, and then click Finish. Note that the resulting chart contains two data points instead of four.

9. Save the form as frmChartForm.

1. In the Database window, click the Forms tab, and then click New.

2. In the New Form dialog box, click Chart Wizard and choose tblChartData from the list of tables and queries. Click OK.

3. Move TransDate and TransAmt from the Available Fields list to the list of fields for the chart. Click Next.

4. When asked what type of chart you would like, click the Line Chart button, and then click Next.

5. In the layout screen, double-click TransDate by month.

6. In the Group dialog box, click Day, and then click OK. Leave SumOfTransAmt unchanged in the layout screen and click Finish. Note that the resulting chart contains two data points instead of four.

7. Save the form as frmChartForm.

Change the Chart's Underlying SQL Statement

1. Open frmChartForm in Design view.

2. Right-click the chart, and then click Properties.

3. Click the Row Source box and then click the Build (...) button to open the Query Builder.

4. On the View menu, click Totals to remove the GROUP BY clause from the SQL statement of the query.

5. On the File menu, click Close. When you are prompted to update the property, click Yes.

6. On the View menu, click Form. Note that the chart contains four data points and that each date appears twice on the x axis.

REFERENCES

You can also create and save a query and then use that query as the row source of the chart. For more information about how to use a saved query as the row source of a chart, please see the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: Q109315
   TITLE     : ACC: How to Change the Order of Columns in a Chart (1.x/2.0)

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