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 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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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),...
|
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
|
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...
|
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....
|
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...
|
by: Kent Eilers |
last post by:
The following query changes when I save it:
==================================================================
SELECT
AcctID
FROM
(SELECT
as AcctID
FROM tblOrderHeader
UNION
|
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.
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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...
|
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...
| |