best way to graph stacked bar with line on two axes

best way to graph stacked bar with line on two axes

Post by elby » Wed, 15 Jul 2009 12:47:05

I have been scratching my head on this all day. I'm working in Access
2003. I have a query that produces volume and percent time spent for
two quarters, with 3 business lines over the the two quarters. I want
to graph this as a stacked combo chart. It would have lines for
percent time plotted on the right axis and volume in stacked columns
plotting on the left axis. At the bottom of the chart would be Q1 and
Q2, and the legend would show the columns and lines for each of the
business lines.

Solution 1 was to create a pivotchart off the query. That was fairly
simple except it required a lot of formatting: recoloring the series,
applying and formatting data labels, and turning the series into lines
to plot onto the second axis. But now it's complete and I can't do
anything with the resulting chart because it doesn't export to a
report. Also, there are some leftover fields like the Filter field
that I want to remove, but don't know how.

So here's my question: Should I recreate the pivotchart as a form and
then import it into my report as a subreport? If so, can someone point
me to some beginner level VBA specific to pivotcharts where I can code
the colors for the chart and set all the datalabels? It was tedious
the first time. I imagine there might be some kind of command I can
put in the immediate window to set data label font and apply colors
with RGB values, but I don't know where to start.

If that isn't the way to go, is there a better way to make this graph?
Thank you,