473,326 Members | 2,813 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

SQL Query looses order after UNION in A97

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
1 6158
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Stephen Miller | last post by:
Hi, I am trying to add a staggered running total and average to a query returning quarterly CPI data. I need to add 4 quarterly data points together to calculate a moving 12-month sum (YrCPI),...
4
by: techquest | last post by:
got slightly similar table. #Temp Table: name,name1,name2,exam,score A,A1,A21, A,A1,A21,math100,88 A,A1,A21,math101,56 A,A1,A21, A,A1,A21,math102,67 A,A1,A21, A,A1,A21,math104,45
5
by: NAJH | last post by:
I've been trying to do a union with a subquery - I've made a different example which follows the same principles as follows: First bit brings back accounts which are in the top 10 to 15 by...
5
by: andreas.muller | last post by:
Hello everyone, I'm trying to solve this problem but can't seem to figure out how to start. I would like to create a rating system where people can vote (1-5 stars) on randomly displayed items....
3
by: Dalan | last post by:
From reading Access 97 help text, it seems that to do what I need to do will require a Union Query. As this would be my first, I think I might require a little guidance. I have two tables with...
4
by: Kent Eilers | last post by:
The following query changes when I save it: ================================================================== SELECT AcctID FROM (SELECT as AcctID FROM tblOrderHeader UNION
2
by: S. van Beek | last post by:
Dear reader, The following code delivers a wild card in the result of the query. But the ORDER BY is not longer working.
7
by: serge | last post by:
How can I run a single SP by asking multiple sales question either by using the logical operator AND for all the questions; or using the logical operator OR for all the questions. So it's always...
1
by: bgreenspan | last post by:
Hi Everyone, I'm back for some more expert help. Here's what I am doing and what I tried. My database has entries with Contract Names and Expiry Dates, among other fields. I have a form...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.