[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

toughie? building a chart of some sorts.

Posted on 2004-09-13
23
Medium Priority
?
347 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

656 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