By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,122 Members | 927 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,122 IT Pros & Developers. It's quick & easy.

SQL Query looses order after UNION in A97

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
jc****@tpg.com.au (Jeff Blee) wrote in
news:38**************************@posting.google.c om:
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.


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.
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.