[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 350
  • Last Modified:

toughie? building a chart of some sorts.

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
engineroom
Asked:
engineroom
  • 10
  • 7
  • 5
1 Solution
 
fritz_the_blankCommented:
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
 
fritz_the_blankCommented:
You could put something like that in a loop that traverses an array of month values.

FtB
0
 
alorentzCommented:
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
engineroomAuthor Commented:
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
 
engineroomAuthor Commented:
let me see this alorentz, thanx guys, funny thing is that i was waiting for you two guys to answer. :-D
0
 
alorentzCommented:
If you have Access, you can use the Crosstab Wizard to help you with the SQL.
0
 
engineroomAuthor Commented:
how would i build my tables alorentz? how do i make it so that every new year i can add the new year?
0
 
fritz_the_blankCommented:
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
 
alorentzCommented:
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
 
engineroomAuthor Commented:
One more thing, can i display this information with asp in that format?
0
 
alorentzCommented:
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
 
alorentzCommented:
>>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
 
engineroomAuthor Commented:
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
 
fritz_the_blankCommented:
You can use an ORDER BY clause.

alorentz already showed you how to display this above.

FtB
0
 
alorentzCommented:
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
 
fritz_the_blankCommented:



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
 
alorentzCommented:
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
 
alorentzCommented:
engineroom - do you need further assistance?
0
 
engineroomAuthor Commented:
no, i'm good man, thanx for all your help.
0
 
alorentzCommented:
OK, good luck!
0
 
engineroomAuthor Commented:
thx all!!
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 10
  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now