Add Second Series to Graph

[Dave]

Hi All,

I am really struggling and need some help please.

I want to produce a Stacked Column Chart in Access 2007.

I have 4 bits of data:

Count KO/G
Count KO/A
Count KO/R
Number of Actual KO

I want the first column to be stacked: Count KO/G + Count KO/A + Count KO/R

and the second column to be: Number of Actual KO

I have managed to stack Count KO/G + Count KO/A + Count KO/R in a single
stacked column chart but cannot add the second one.

The first one looks like this:

SELECT Null AS Expr1, RAG_KO.[Count KO/G], RAG_KO.[Count KO/A],
RAG_KO.[Count KO/R]
FROM RAG_KO;



Any assistance much appreciated.

Thanks

 

[Techie_no1]

You'll need to join the results of the SQL query with that of a second query using a UNION. MS Graph will take the results of each query and create a separate series for it.

SELECT Null AS Expr1, RAG_KO.[Count KO/G], RAG_KO.[Count KO/A],
RAG_KO.[Count KO/R]
FROM RAG_KO
UNION
SELECT Null AS Expr1, RAG_KO.[Number of Actual KO]
FROM RAG_KO;

It works for most types of graphs but there is a bit of strangeness when creating a column-line type.

I'm trying to show a series of data as columns and the average as a line but no matter what I try the average ends up as a bar and my data as the line. I'm still working on that "quirk".

Dale Beaudette
AECL Ltd.
(Microsoft MVP Windows Shell/User 2002/2003)

 

[Techie_no1]

With the help of a co-worker (Frank Brown) we solved the column-line problem by using an "Order By" setting actual values (Avg and Total) for Expr1 instead of using NULL. This allows us to determine which row is returned to MS Graph first.

Dale Beaudette
AECL Ltd.
Chalk River Laboratories

Microsoft MVP - Windows Shell/User 2002/2003