HOW TO: Create a Chart That Does Not Summarize Data in Access 2000

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 that contains 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. You must change the SQL statement on which the chart is based to prevent the data from being summarized.

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 "SumOfFieldName" 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 then click OK, and if the axis box contains a field whose data type is Date/Time, you receive the following error message when you click Next to advance to the next screen of the chart wizard:

The following example demonstrates how to prevent the summarization of data by removing the GROUP BY clause from the SQL statement in the RowSource property of the chart.

Create the Data

1. Start Microsoft Access and open any database.
2. Create the following table:
   Table: tblChartData
   
   Field Name: TransDate
   Data Type: Date/Time
   
   Field Name: TransAmt
   Data Type: Number
3. Save the table as tblChartData, and then when you are prompted to create a primary key, click No.
4. Switch to Datasheet view and type the following records:

      TransDate    TransAmt
      ---------    --------
      1/1/2000         9
      1/1/2000         7
      1/5/2000         8
      1/5/2000         6
   					

5. Close the table.

Create the Chart

To create a chart based on the tblChartData table, follow these steps:

1. In the Database window, click Forms, and then click New.
2. In the New Form dialog box, click Chart Wizard, and then select 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. On the layout screen, double-click TransDate by month.
6. In the Group dialog box, click Day, and then click OK. Leave SumOfTransAmt unchanged on the layout screen, and then click Finish. Note that the resulting chart contains two data points instead of four because the data has been summarized.
7. Save the form as frmChartForm.

Change the Chart's Underlying SQL Statement

1. Open the frmChartForm form in Design view.
2. Right-click the chart, and then click Properties if they are not already visible.
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 -- two data points for the date 1/1/2000 and two data points for the date 1/5/2000.

You can also create and save a query and then use that query as the row source of the chart. For additional information about how to use a saved query asthe row source of a chart, click the article number below to view the article in the Microsoft Knowledge Base: