Plot an average line on a bar chart report

  • Thread starter Thread starter Elaine
  • Start date Start date
E

Elaine

I am wondering if it is possible to plot a straight
line on a bar chart in Access report. I need to have this line plotted
at the
average value, so those bars above average can be easily spotted above
the plotted average line. Any help would be appreciated.

Elaine
 
Elaine,
Without knowing how your data is organized/displayed, I'm not sure what you mean by
"average."

If the average is across all series...
Add a calculated field to your chart query that averages the values, and plot it.

Or, using that calculated average, try a TrendLine. One Trendline format is a
straight line that indicates wheteher the average is rising or falling, and your value
series would plot above or below that Trendline.
 
Thank you for helping me. I tried the trendline, but I can get the
straight line. Can you walk me through this?

Thank you!

Elaine
 
Here is a possible solution. I opened Northwind to create a graph of total
freight cost by month from the Orders table.
1) Create a totals query
==qtotFreightByMonth=========
SELECT (Format([OrderDate],"mmm"" '""yy")) AS [Order Month],
Sum(Orders.Freight) AS [Total Freight]
FROM Orders
GROUP BY (Format([OrderDate],"mmm"" '""yy")),
(Year([OrderDate])*12+Month([OrderDate])-1),
Format([OrderDate],"yyyy mm")
ORDER BY Format([OrderDate],"yyyy mm");
=======================
2) set your charts Row Source property to:
===========================
SELECT qtotFreightByMonth.[Order Month],
qtotFreightByMonth.[Total Freight],
(SELECT AVG([Total Freight])
FROM qtotFreightByMonth) AS [Mth Avg]
FROM qtotFreightByMonth;
===========================
You will end up with the months as the X axis and two lines (Total Freight
and Mth Avg).
 
Elaine,
Give Duane's suggestion a try...

Here's how I did it though. I created a pass thru query that yields just one field...
the average you want. (ex. AvgSales = 350.0)
I then brought that table into my chart query and used a cartesian realationship so
that that value was applied/related to every row in your original chart values.

A Cartesian realtionship is just your original table and the pass thru table with NO
realtionship at all. (No Join at all)

Simple example. If your old plotted data was...
Month Sales
Jan 100
Feb 200
Mar 300

With the unrelated pass thru field added to the query...
Month Sales AvgSales
Jan 100 350
Feb 200 350
Mar 400 350

AvgSales can now be plotted as a straight line.
 
Duane and Al:

Thank you both very much. I am going to try these two ways, and will
let you know if I get it or not.

Have a good weekend!

E
 
Duane and Al:

I tried these two ways, they both worked! Althought it is not a line,
but it looks good!

Thanks!

E
 
What is it if not a line?

In Chart design, select that series.
Right click while selected, and select Chart Type
Select the Line Graph Type
That should make just that series a line, and you can format the line further as to
Pattern/Marker...etc
 
Elaine said:
I can make it either both bar chart or both line chart, but not bar and
line mix.

If you select "chart type" while the chart is selected then you will change
both. If you only have one series selected then "chart type" should only affect
that one series.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top