I hope someone can help me get this graph outputing in proper order.
After help from Tom, I got a graph to display output from the previous
12 months and include the average of that output all in the one graph.
The output was in the order of the months, but after unioning with the
averages SQL code, the order is lost. Below is the full sql code that
is the data source for the graph:

SELECT (Format([Problem Management].[Date],"mmm"" '""yy")) AS Month,
[Problem Management].S1S_Num_Problems AS Problems, [Problem
Management].S1S_Avg_Res_Time AS [Avg Rest Time], [Problem
Management].S1S_Exceptions AS Exceptions
FROM [Problem Management]
WHERE (((DateDiff("m",Date(),([Problem Management].[Date])))>-13))
ORDER BY [Problem Management].Date
UNION
SELECT "Avg" AS Month, Avg(qryS1S.Problems) AS Problems,
Avg(qryS1S.[Avg Rest Time]) AS [Avg Rest Time] ,
Avg(qryS1S.Exceptions) AS Exceptions
FROM qryS1S;

The qryS1S query that is mentioned is exactly the same as the first
part of this query before the union.
The above query makes a graph exactly as I want, except the months
along the x axis of the graph are in alphabetical order i.e. Apr2003
Aug2003 avg Dec2002 Feb2003 etc.. . .

I have tried everything i can think of, but to no avail. It is
probably loosing order because I change "Date" to "Month", but I need
to do this to get the averages into the graph as a column. I couldn't
seem to get the union to work otherwise. Basically I just need to put
the averages column at the right hand end of the first query, which is
simple in Excel but it is turning in to quite a bit of a hassle in
Access.
Thank you for any help you can give me.

Jeff Blee

jcblee (AT) tpg (DOT) com.au (Jeff Blee) wrote in
news:38b0b6e1.0310010513.3ff366b7 (AT) posting (DOT) google.com:

When you create a union query, it reorders the records across the
fields as it eliminates duplicate rows. Idont know if the
UNION SELECT ALL statement prevents resequencing, you could try
that.

The simple out of this is to add a calculated field to each select
statement that returns a numeric value that will sort your query.
You can then ignore it in further processing.


The first SELECT statement would get Format([Problem
Management].[Date],"yyyymm") as myorder, and the rest of your
existing statement.
The second SELECT would be Format(now().[Date],"yyyy") & "AA" as
myorder, "Avg" AS Month, Avg(qryS1S.Problems) AS Problems,
Avg(qryS1S.[Avg Rest Time]) AS [Avg Rest Time] ,
Avg(qryS1S.Exceptions) AS Exceptions
FROM qryS1S;

Bob Q.