Charts – Plotting Dates on the X and Y axis (Access 2000)

Topic

New Reply

Is there a way to plot dates on both the X and Y axis using the Chart Wizard. I am trying to graphically depict the change of a milestone due date over time. The X axis details the change in time monthly. The Y axis would reflect the current milestone dates as they change month to month. The series is several significant milestones. The graph would detail the change of the milestone dates over time such that is the date decreased, the graph would slope downwoards. I am having problems with the Y axis date values and have tried bringing in the dates as numbers. Any ideas?

Reply | Quote

Replies

Charts normally combine like dates on the X axis and then summarize Y axis values. In other words if you had dates for the X axis, and currency values for the Y axis, a chart would normally combine like dates into one date and then add, count, min, max, etc… on the Y axis values. For example, say you have two records that both have the date 01/01/2001. One record has a currency value of $5.00 and the other has $10.00. Your chart would (if sum) show only one date and value: 01/01/2001 $15.00. I think if you change the query the chart is based on, so that it does not “Group By” (thus eliminating any type of summarization), then your chart just might work. Try these steps (backup your chart first):
Change the values for the Y axis from numbers back to date (as you originally had them).
Open your chart form in Design view.
Right-click the chart, and then click Properties if they are not already visible.
Click the Row Source box, and then click the Build (…) button to open the Query Builder.
On the View menu, click Totals to remove the GROUP BY clause from the SQL statement of the query.
On the File menu, click Close . When you are prompted to update the property, click Yes .
Now view the chart in Form view. Did this help?

Reply | Quote

Great idea, however, it did not work. The properties do not allow the group by to be removed since the chart is based on a pivot table and the group by is required. (The group by is greyed out, it can’t be removed.)

While working on the problem, I did get it to work by doing the following. In the query, I converted the date to a serial number. I then based the chart on the query with the serial number. After creating the chart, in the chart properties, design view, I modified the Y axis date field properties such that the property is a date. The chart did appear to create as required. Of course, I was in a hurry and need to re-try this later to see if I can do it again.

Thanks for the suggestions.

Reply | Quote