Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL/Charts??

Author  Topic 

desperate
Starting Member

21 Posts
Posted - 2008-10-23 : 10:15:09
Hey All!

I've posted in here once before needing help on getting my months in a query to align with a specific average.
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=112696&SearchTerms=desperate[/url]

Now I'm moving on to problem #2.. When I take this information from my query to make into a chart, the months along the bottom of the chart will ONLY go in ascending order. Here is what I'm talking about.

This is the SQL query that I'm pulling from to make the chart:
SELECT T.month, T.[AvgOfDIFF DATE]
FROM [SELECT Avg([MR to PO JAN Query].[DIFF DATE]) AS [AvgOfDIFF DATE], 1 as month_value,'Jan' AS [month]
FROM [MR to PO JAN Query]
UNION SELECT Avg([MR to PO FEB Query].[DIFF DATE]) AS [AvgOfDIFF DATE1],2,'Feb' FROM [MR to PO FEB Query]
UNION SELECT Avg([MR to PO MAR Query].[DIFF DATE]) AS [AvgOfDIFF DATE2],3,'Mar'
FROM [MR to PO MAR Query]
UNION SELECT Avg([MR to PO APR Query].[DIFF DATE]) AS [AvgOfDIFF DATE3],4,'Apr'FROM [MR to PO APR Query]
UNION SELECT Avg([MR to PO MAY Query].[DIFF DATE]) AS [AvgOfDIFF DATE4],5,'May'FROM [MR to PO MAY Query]
UNION SELECT Avg([MR to PO JUN Query].[DIFF DATE]) AS [AvgOfDIFF DATE5],6,'Jun'
FROM [MR to PO JUN Query]
UNION SELECT Avg([MR to PO JUL Query].[DIFF DATE]) AS [AvgOfDIFF DATE6],7,'Jul'FROM [MR to PO JUL Query]
UNION SELECT Avg([MR to PO AUG Query].[DIFF DATE]) AS [AvgOfDIFF DATE7],8,'Aug'FROM [MR to PO AUG Query]
UNION SELECT Avg([MR to PO SEP Query].[DIFF DATE]) AS [AvgOfDIFF DATE8],9,'Sep'FROM [MR to PO SEP Query]
]. AS T
ORDER BY T.month_value;


Here is the way the chart looks:



Is there a way (in SQL view) to set my query so that when I make it into a chart it will automatically align the dates up in correct order on the x-axis? Or am I not doing something correctly?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-10-23 : 10:18:30
pplot on basis of monthvalue rather than month column. But for labels select month field itself.
Go to Top of Page

desperate
Starting Member

21 Posts
Posted - 2008-10-23 : 10:19:42
quote:
Originally posted by visakh16

pplot on basis of monthvalue rather than month column. But for labels select month field itself.


Sorry if I seem N00Bie haha, but basis??
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-10-23 : 10:21:18
quote:
Originally posted by desperate

quote:
Originally posted by visakh16

pplot on basis of monthvalue rather than month column. But for labels select month field itself.


Sorry if I seem N00Bie haha, but basis??

what is the field that you currently selected for x axis of chart? i was telling you to select monthvalue for x axis
Go to Top of Page

desperate
Starting Member

21 Posts
Posted - 2008-10-23 : 10:23:03
quote:
Originally posted by visakh16

quote:
Originally posted by desperate

quote:
Originally posted by visakh16

pplot on basis of monthvalue rather than month column. But for labels select month field itself.


Sorry if I seem N00Bie haha, but basis??

what is the field that you currently selected for x axis of chart? i was telling you to select monthvalue for x axis


All I did was use the chart wizard, put the "months" value on the x-axis and average on the y. I figured it was something with the coding with the SQL in the query itself.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-10-23 : 10:36:57
yup. i'm telling you to use monthvalue in x-axis and average on y. is it still unclear?
Go to Top of Page

desperate
Starting Member

21 Posts
Posted - 2008-10-23 : 10:49:41
quote:
Originally posted by visakh16

yup. i'm telling you to use monthvalue in x-axis and average on y. is it still unclear?


When I go to the chart wizard to create it, "month value" isnt an option to bring over to even add to my chart. I can only bring months and averages over.. hm..
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-10-23 : 10:52:37
quote:
Originally posted by desperate

quote:
Originally posted by visakh16

yup. i'm telling you to use monthvalue in x-axis and average on y. is it still unclear?


When I go to the chart wizard to create it, "month value" isnt an option to bring over to even add to my chart. I can only bring months and averages over.. hm..

ah. i got the reason. change query as below and then try

SELECT T.month, T.[AvgOfDIFF DATE],T.month_valueFROM 
(SELECT Avg([MR to PO JAN Query].[DIFF DATE]) AS [AvgOfDIFF DATE], 1 as month_value,'Jan' AS [month]
FROM [MR to PO JAN Query]
UNION SELECT Avg([MR to PO FEB Query].[DIFF DATE]) AS [AvgOfDIFF DATE1],2,'Feb' FROM [MR to PO FEB Query]
UNION SELECT Avg([MR to PO MAR Query].[DIFF DATE]) AS [AvgOfDIFF DATE2],3,'Mar'
FROM [MR to PO MAR Query]
UNION SELECT Avg([MR to PO APR Query].[DIFF DATE]) AS [AvgOfDIFF DATE3],4,'Apr'FROM [MR to PO APR Query]
UNION SELECT Avg([MR to PO MAY Query].[DIFF DATE]) AS [AvgOfDIFF DATE4],5,'May'FROM [MR to PO MAY Query]
UNION SELECT Avg([MR to PO JUN Query].[DIFF DATE]) AS [AvgOfDIFF DATE5],6,'Jun'
FROM [MR to PO JUN Query]
UNION SELECT Avg([MR to PO JUL Query].[DIFF DATE]) AS [AvgOfDIFF DATE6],7,'Jul'FROM [MR to PO JUL Query]
UNION SELECT Avg([MR to PO AUG Query].[DIFF DATE]) AS [AvgOfDIFF DATE7],8,'Aug'FROM [MR to PO AUG Query]
UNION SELECT Avg([MR to PO SEP Query].[DIFF DATE]) AS [AvgOfDIFF DATE8],9,'Sep'FROM [MR to PO SEP Query]
) AS T
ORDER BY T.month_value;
Go to Top of Page

desperate
Starting Member

21 Posts
Posted - 2008-10-23 : 11:01:28
quote:
Originally posted by visakh16

quote:
Originally posted by desperate

quote:
Originally posted by visakh16

yup. i'm telling you to use monthvalue in x-axis and average on y. is it still unclear?


When I go to the chart wizard to create it, "month value" isnt an option to bring over to even add to my chart. I can only bring months and averages over.. hm..

ah. i got the reason. change query as below and then try

SELECT T.month, T.[AvgOfDIFF DATE],T.month_valueFROM 
(SELECT Avg([MR to PO JAN Query].[DIFF DATE]) AS [AvgOfDIFF DATE], 1 as month_value,'Jan' AS [month]
FROM [MR to PO JAN Query]
UNION SELECT Avg([MR to PO FEB Query].[DIFF DATE]) AS [AvgOfDIFF DATE1],2,'Feb' FROM [MR to PO FEB Query]
UNION SELECT Avg([MR to PO MAR Query].[DIFF DATE]) AS [AvgOfDIFF DATE2],3,'Mar'
FROM [MR to PO MAR Query]
UNION SELECT Avg([MR to PO APR Query].[DIFF DATE]) AS [AvgOfDIFF DATE3],4,'Apr'FROM [MR to PO APR Query]
UNION SELECT Avg([MR to PO MAY Query].[DIFF DATE]) AS [AvgOfDIFF DATE4],5,'May'FROM [MR to PO MAY Query]
UNION SELECT Avg([MR to PO JUN Query].[DIFF DATE]) AS [AvgOfDIFF DATE5],6,'Jun'
FROM [MR to PO JUN Query]
UNION SELECT Avg([MR to PO JUL Query].[DIFF DATE]) AS [AvgOfDIFF DATE6],7,'Jul'FROM [MR to PO JUL Query]
UNION SELECT Avg([MR to PO AUG Query].[DIFF DATE]) AS [AvgOfDIFF DATE7],8,'Aug'FROM [MR to PO AUG Query]
UNION SELECT Avg([MR to PO SEP Query].[DIFF DATE]) AS [AvgOfDIFF DATE8],9,'Sep'FROM [MR to PO SEP Query]
) AS T
ORDER BY T.month_value;



I get the error "This operation is not allowed in subqueries"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-10-23 : 11:18:23
show the query please?
Go to Top of Page

desperate
Starting Member

21 Posts
Posted - 2008-10-23 : 11:41:13
quote:
Originally posted by visakh16

show the query please?


Here's the current SQL for it:

SELECT T.month, T.[AvgOfDIFF DATE]
FROM [SELECT Avg([MR to PO JAN Query].[DIFF DATE]) AS [AvgOfDIFF DATE], 1 as month_value,'Jan' AS [month]
FROM [MR to PO JAN Query]
UNION SELECT Avg([MR to PO FEB Query].[DIFF DATE]) AS [AvgOfDIFF DATE1],2,'Feb' FROM [MR to PO FEB Query]
UNION SELECT Avg([MR to PO MAR Query].[DIFF DATE]) AS [AvgOfDIFF DATE2],3,'Mar'
FROM [MR to PO MAR Query]
UNION SELECT Avg([MR to PO APR Query].[DIFF DATE]) AS [AvgOfDIFF DATE3],4,'Apr'FROM [MR to PO APR Query]
UNION SELECT Avg([MR to PO MAY Query].[DIFF DATE]) AS [AvgOfDIFF DATE4],5,'May'FROM [MR to PO MAY Query]
UNION SELECT Avg([MR to PO JUN Query].[DIFF DATE]) AS [AvgOfDIFF DATE5],6,'Jun'
FROM [MR to PO JUN Query]
UNION SELECT Avg([MR to PO JUL Query].[DIFF DATE]) AS [AvgOfDIFF DATE6],7,'Jul'FROM [MR to PO JUL Query]
UNION SELECT Avg([MR to PO AUG Query].[DIFF DATE]) AS [AvgOfDIFF DATE7],8,'Aug'FROM [MR to PO AUG Query]
UNION SELECT Avg([MR to PO SEP Query].[DIFF DATE]) AS [AvgOfDIFF DATE8],9,'Sep'FROM [MR to PO SEP Query]
]. AS T
ORDER BY T.month_value;

& here it is in regular view:
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-10-23 : 11:45:49
this is working for me anyways
SELECT T.month, T.[AvgOfDIFF DATE],T.month_value
FROM [SELECT Avg([MR to PO JAN Query].[DIFF DATE]) AS [AvgOfDIFF DATE], 1 as month_value,'Jan' AS [month]
FROM [MR to PO JAN Query]
UNION SELECT Avg([MR to PO FEB Query].[DIFF DATE]) AS [AvgOfDIFF DATE1],2,'Feb' FROM [MR to PO FEB Query]
UNION SELECT Avg([MR to PO MAR Query].[DIFF DATE]) AS [AvgOfDIFF DATE2],3,'Mar'
FROM [MR to PO MAR Query]
UNION SELECT Avg([MR to PO APR Query].[DIFF DATE]) AS [AvgOfDIFF DATE3],4,'Apr'FROM [MR to PO APR Query]
UNION SELECT Avg([MR to PO MAY Query].[DIFF DATE]) AS [AvgOfDIFF DATE4],5,'May'FROM [MR to PO MAY Query]
UNION SELECT Avg([MR to PO JUN Query].[DIFF DATE]) AS [AvgOfDIFF DATE5],6,'Jun'
FROM [MR to PO JUN Query]
UNION SELECT Avg([MR to PO JUL Query].[DIFF DATE]) AS [AvgOfDIFF DATE6],7,'Jul'FROM [MR to PO JUL Query]
UNION SELECT Avg([MR to PO AUG Query].[DIFF DATE]) AS [AvgOfDIFF DATE7],8,'Aug'FROM [MR to PO AUG Query]
UNION SELECT Avg([MR to PO SEP Query].[DIFF DATE]) AS [AvgOfDIFF DATE8],9,'Sep'FROM [MR to PO SEP Query]
]. AS T
ORDER BY T.month_value;
Go to Top of Page

desperate
Starting Member

21 Posts
Posted - 2008-10-23 : 11:50:29
quote:
Originally posted by visakh16

this is working for me anyways
SELECT T.month, T.[AvgOfDIFF DATE],T.month_value
FROM [SELECT Avg([MR to PO JAN Query].[DIFF DATE]) AS [AvgOfDIFF DATE], 1 as month_value,'Jan' AS [month]
FROM [MR to PO JAN Query]
UNION SELECT Avg([MR to PO FEB Query].[DIFF DATE]) AS [AvgOfDIFF DATE1],2,'Feb' FROM [MR to PO FEB Query]
UNION SELECT Avg([MR to PO MAR Query].[DIFF DATE]) AS [AvgOfDIFF DATE2],3,'Mar'
FROM [MR to PO MAR Query]
UNION SELECT Avg([MR to PO APR Query].[DIFF DATE]) AS [AvgOfDIFF DATE3],4,'Apr'FROM [MR to PO APR Query]
UNION SELECT Avg([MR to PO MAY Query].[DIFF DATE]) AS [AvgOfDIFF DATE4],5,'May'FROM [MR to PO MAY Query]
UNION SELECT Avg([MR to PO JUN Query].[DIFF DATE]) AS [AvgOfDIFF DATE5],6,'Jun'
FROM [MR to PO JUN Query]
UNION SELECT Avg([MR to PO JUL Query].[DIFF DATE]) AS [AvgOfDIFF DATE6],7,'Jul'FROM [MR to PO JUL Query]
UNION SELECT Avg([MR to PO AUG Query].[DIFF DATE]) AS [AvgOfDIFF DATE7],8,'Aug'FROM [MR to PO AUG Query]
UNION SELECT Avg([MR to PO SEP Query].[DIFF DATE]) AS [AvgOfDIFF DATE8],9,'Sep'FROM [MR to PO SEP Query]
]. AS T
ORDER BY T.month_value;



The error I get on that one is "Syntax error in FROM clause" This is so difficult, haha.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-10-23 : 14:28:34
where are you typing query? I've tried in Mgmnt studio editor.
Go to Top of Page

desperate
Starting Member

21 Posts
Posted - 2008-10-23 : 14:31:15
quote:
Originally posted by visakh16

where are you typing query? I've tried in Mgmnt studio editor.


I'm using Access 2003.. so everything is opened in there??
Go to Top of Page

peter134
Starting Member

3 Posts
Posted - 2008-10-23 : 18:21:18
hi there
i need a bit help with my query
can you help me
let me know i will write
the query
regards
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site