Deep breath... Chart WITHOUT aggregate function

  • Thread starter Thread starter Darrell
  • Start date Start date
D

Darrell

It appears that the Chart Wizard in Access does not know how to function with
a simple linear record set. I would like to display a series of water level
data points for a single well in a straightforward line chart. Is this
possible? (?????????????) (Sorry for the sarcasm - I'm trying to calm down
from being exTREMEly frustrated.)

Thanks for any and all help.
 
I know I have a chart that just displays 2 bars (actual and goal) from two
records
TheText, TheValue
Actual 100
Goal 150

Rowsource =
Select TheText, TheValue from tblData

Hope that helps,
Mark
 
Thanks, Mark, for the response. Here is my data...

DATE Elevation
4/7/1993 2150.66900053406
10/29/1993 2133.95899954224
11/19/1993 2133.03900137329
1/8/1994 2131.2589987793
4/15/1994 2128.45899954224
9/21/1994 2114.23899832153
3/27/1995 2153.35899916077
4/27/1995 2149.95899954224
5/26/1995 2146.25900068665
6/16/1995 2143.99900045776

How do I go about getting this simple data set to create a simple line
graph?? Given the query resulting in this data, the wizard lays this out:

y axis: SUM of elevation (and AGGREGATE function)
x axis: Date BY MONTH (again, an AGGREGATE function)
series: Just the word "Series"

If I drag "Sum of elevation" from the y axis box, it leaves the word "Data"
in this box and I now have "Data" over the y axis and "Series" to the right
of the chart. If I then drag "Elevation" onto the Series box, I now have
"Data" over the y axis, "Elevation" in the Series box, and Date BY MONTH over
the x axis box.

When I click "Finish" and (in this case) run the report, the result is a
graph with Mo/Yr represented along the x axis, each of the 10 values
represented as its own series symbol, with a legend displaying the data-point
value, and a scale along the y axis running from 0 to 1.2 (the scaling
depends on the physical size of the chart on the page), and the series
symbols all lined up neatly exactly on the "1" grid line.

Obviously, the wizard and I are not communicating. This is the second
attempt I have made (after a 4-yr hiatus) to create simple charts in Access,
with exactly the same results. There is something FUNDAMENTAL that I am not
understanding, and I'm just about to shoot either myself or the program.
So... your intervention just might be life saving (and I'm not going to
predict which life you're saving :) ).

Thanks for any and all help.
 
Ah... I did just make one breakthrough... the box to the right of the chart
is not asking for the data to be plotted, it's asking for the legend text.
It's the box over the y axis that's asking for the data to be plotted (I'm
not sure how this is supposed to be intuitively obvious, but I got there by
process of elimination). By the way, a necessary precursor to this
breakthrough was the inclusion of the well in the data set, so that I HAD
something to put in a legend (I always thought legends were optional).

One thing this is still doing is labeling the x axis with mo/yr rather than
as the actual dates in the data set. This might actually be useful, but I
would certainly like the option of having it do exactly what I asked it to,
which is put the actual dates on the x axis and plot them against the values.
Is there a way to do this?

Thanks again.
 
If you open your form in design view and open the properties
sheet for the chart and find the Row Source property you will
see the query that the chart is based on. If you click the build
(...) button at the right of that row it will open this query in
design view and you can change the fields to display how you
actually want them rather than how the ridiculously unhelpful
chart wizard randomly decided it thought you wanted them.

In other words it probably formatted your date field with
something like;

Format([YourDateField], "mmm yy")

You can remove the format and just display the field contents.
You also can change the Elevation from Sum to Group By if
you need, etc.

In addition to that, if you double click the chart in design view, then
right click and go to Chart Options, there are some options you can
modify there as well. For example, you may need to go to the Axes
tab and change the Category(x) Axis from Automatic to Category or
other things like that.

Eventually you'll get the chart you want, no thanks to the chart wizard.
 
Back
Top