?
Solved

toughie? building a chart of some sorts.

Posted on 2004-09-13
23
Medium Priority
?
346 Views
Last Modified: 2012-05-05
hey all, i'm trying to build a chart of some sorts. This is what i want it to look like

month        2004       2003       2002      2001       Average (3)

Jan           42332     43228       9827     8892

Feb

etc.

i need to have all 12 months in there and also, the count for each year is being pulled from a database that looks like this...         Month | Year | Count

so i have to dynamically do this based on my current year. so i have to grab the counts for each month and year, but when we get to september, there won't be a count for 2004 cause the month isn't over. Keep this in mind cause when we go to the average column, we have to average the last 3 years of months. How do i get this going? Thanx a lot!!!!
0
Comment
Question by:engineroom
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 7
  • 5
23 Comments
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12046781
I am guessing that you are going to need more than one sql select to get this right. Perhaps you should consider something like this:

strSQL = "SELECT Sum(Count) FROM tblYourTable Group By YEAR Having Month='January'"

FtB
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12046789
You could put something like that in a loop that traverses an array of month values.

FtB
0
 
LVL 31

Expert Comment

by:alorentz
ID: 12046831
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Author Comment

by:engineroom
ID: 12046859
i tried, i just can't get it. how do i put it in a loop. Especially based on what year and month it is today. Each row would have to have some sort of function.. .i.e.

                  2004
jan         getCount(month,year)


function getCount(month,year)

select from count where month = month and year = year

end function

something like that right? but how do i get the row to know what year row it's in and what month row it's in, all while being in a loop?
0
 
LVL 3

Author Comment

by:engineroom
ID: 12046870
let me see this alorentz, thanx guys, funny thing is that i was waiting for you two guys to answer. :-D
0
 
LVL 31

Expert Comment

by:alorentz
ID: 12046895
If you have Access, you can use the Crosstab Wizard to help you with the SQL.
0
 
LVL 3

Author Comment

by:engineroom
ID: 12046917
how would i build my tables alorentz? how do i make it so that every new year i can add the new year?
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12046933
Let's keep the adding of data separate than the reporting of it.

If you go with the Crosstab, you can just report off of your table as you have it. You can continue to add data to the table the way that you have already.

FtB
0
 
LVL 31

Accepted Solution

by:
alorentz earned 2000 total points
ID: 12046936
Try this:

TRANSFORM Sum([count]) AS [The Value]
SELECT [Month], Sum([count]) AS [Total Of count]
FROM tblTest
GROUP BY [Month]
PIVOT [year]


Will Display this:

Month      Total       2002      2003      2004
February      110      110            
January      70      50      20
March      40                            40


This is what a Crosstab query is for....
0
 
LVL 3

Author Comment

by:engineroom
ID: 12046946
One more thing, can i display this information with asp in that format?
0
 
LVL 31

Expert Comment

by:alorentz
ID: 12046956
I just built a tbale to match yours:


Month      year      count
January      2004      20
February      2002      10
March      2004      40
January      2003      50
February      2002      100


And what I showed above is the result of the Crsstab query....
0
 
LVL 31

Expert Comment

by:alorentz
ID: 12046991
>>One more thing, can i display this information with asp in that format?

Yes.  Just loop through the fields be field index, not by name:

<%
for each fld in rs.fields
'create your headings
next

do until rs.eof
    for each fld in rs.fields
            'write your rows
    next
  rs.movenext
loop
%>

That's the idea...

0
 
LVL 3

Author Comment

by:engineroom
ID: 12047007
can you explain to me what this means?

TRANSFORM Sum([count]) AS [The Value]
SELECT [Month], Sum([count]) AS [Total Of count]
FROM tblTest
GROUP BY [Month]
PIVOT [year]

????

how do i order by month? if it is at all possible?

and how do i show it in asp?
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12047064
You can use an ORDER BY clause.

alorentz already showed you how to display this above.

FtB
0
 
LVL 31

Expert Comment

by:alorentz
ID: 12047081
Do you have Access?

Above is just a way to show data in different view, it is SQL and difficult to explain to you here.  If you want to learn more, see the links I provided.  But, As far as the query, it should work for what you have now, because I modelled it after your table structure.

And to show in ASP...I already should example of how...
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12047084



I think that the syntax is:

TRANSFORM Sum([count]) AS [The Value]
SELECT [Month], Sum([count]) AS [Total Of count]
FROM tblTest
GROUP BY [Month]
ORDER BY [Month],[Year]
PIVOT [year]

or :

TRANSFORM Sum([count]) AS [The Value]
SELECT [Month], Sum([count]) AS [Total Of count]
FROM tblTest
GROUP BY [Month]
ORDER BY [Year],[Month]
PIVOT [year]


0
 
LVL 31

Expert Comment

by:alorentz
ID: 12047096
Order by month:

TRANSFORM Sum([count]) AS [The Value]
SELECT [Month], Sum([count]) AS [Total Of count]
FROM tblTest
GROUP BY [Month] ORDER BY [Month]
PIVOT [year]
0
 
LVL 31

Expert Comment

by:alorentz
ID: 12047909
engineroom - do you need further assistance?
0
 
LVL 3

Author Comment

by:engineroom
ID: 12047921
no, i'm good man, thanx for all your help.
0
 
LVL 31

Expert Comment

by:alorentz
ID: 12047944
OK, good luck!
0
 
LVL 3

Author Comment

by:engineroom
ID: 12047948
thx all!!
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question