Solved

# toughie? building a chart of some sorts.

Posted on 2004-09-13
325 Views
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
Question by:engineroom
• 10
• 7
• 5

LVL 46

Expert Comment

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

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

FtB
0

LVL 31

Expert Comment

ID: 12046831
0

LVL 31

Expert Comment

ID: 12046849
0

LVL 3

Author Comment

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

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

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

LVL 3

Author Comment

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

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

alorentz earned 500 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

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

LVL 31

Expert Comment

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

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
next

do until rs.eof
for each fld in rs.fields
next
rs.movenext
loop
%>

That's the idea...

0

LVL 3

Author Comment

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

ID: 12047064
You can use an ORDER BY clause.

alorentz already showed you how to display this above.

FtB
0

LVL 31

Expert Comment

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

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

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

ID: 12047909
engineroom - do you need further assistance?
0

LVL 3

Author Comment

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

LVL 31

Expert Comment

ID: 12047944
OK, good luck!
0

LVL 3

Author Comment

ID: 12047948
thx all!!
0

## Featured Post

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than oneâ€¦
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â€¦
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidtâ€¦
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.