Microsoft KB Archive/207636

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

Article ID: 207636

Article Last Modified on 6/28/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q207636



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

This article applies only to a Microsoft Access database (.mdb).

IN THIS TASK
SUMMARY
 * Create the Data
 * Create the Chart
 * Change the Chart's Underlying SQL Statement

REFERENCES



SUMMARY
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 "SumOf " where  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:

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

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.

back to the top

Create the Data
 Start Microsoft Access and open any database. Create the following table:

Table: tblChartData

Field Name: TransDate

Data Type: Date/Time

Field Name: TransAmt

Data Type: Number

 Save the table as tblChartData, and then when you are prompted to create a primary key, click No.  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                     Close the table.

back to the top

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.

back to the top

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.

back to the top

<div class="references_section">