473,703 Members | 2,415 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_Proble ms AS Problems, [Problem
Management].S1S_Avg_Res_Ti me 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.Prob lems) AS Problems,
Avg(qryS1S.[Avg Rest Time]) AS [Avg Rest Time] ,
Avg(qryS1S.Exce ptions) 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 6174
jc****@tpg.com. au (Jeff Blee) wrote in
news:38******** *************** ***@posting.goo gle.com:
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_Proble ms AS Problems,
[Problem Management].S1S_Avg_Res_Ti me 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.Prob lems) AS Problems,
Avg(qryS1S.[Avg Rest Time]) AS [Avg Rest Time] ,
Avg(qryS1S.Exce ptions) 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.Prob lems) AS Problems,
Avg(qryS1S.[Avg Rest Time]) AS [Avg Rest Time] ,
Avg(qryS1S.Exce ptions) 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
5314
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), and then to complicate things, calculate a moving average of the 12-month figure (AvgYrCPI). Given the sample data:
4
1739
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
11072
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 account name. Second bit brings back accounts which are in the bottom 10 to 15 by account name. I want to union the two result sets together. These selects work as
5
4156
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. The randomly displayed items should either have very high ratings OR a very low number of ratings. For example, only return items in the top 20th percentile *OR* items with fewer than 5 votes. The question is, how would I write an SQL query to...
3
10544
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 10 fields that have like data (for instance both have an item description field, an item price field, a general notes field, etc.) but with different field names. The tables have approximately 20/40 other fields that are dissimilar and not needed...
4
1686
by: Kent Eilers | last post by:
The following query changes when I save it: ================================================================== SELECT AcctID FROM (SELECT as AcctID FROM tblOrderHeader UNION
2
4596
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
3388
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 either AND or OR but never mixed together. We can use Northwind database for my question, it is very similar to the structure of the problem on the database I am working on. IF(SELECT OBJECT_ID('REPORT')) IS NOT NULL DROP TABLE REPORT_SELECTION
1
2681
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 designed to show the expiring contracts. To do this I use a straight forward query in my form's ON LOAD code strwhere = " BETWEEN #" & Now() & "# AND #" & DateAdd("m", 6, Now()) & "#" Set MyQueryDef = MyDatabase.CreateQueryDef("qryMattersQuery",...
0
8662
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8961
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7853
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6585
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5922
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4421
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4679
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3114
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2439
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.