Improve company productivity with a Business Account.Sign Up

x
?
Solved

toughie? building a chart of some sorts.

Posted on 2004-09-13
23
Medium Priority
?
351 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
  • 10
  • 7
  • 5
22 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 Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Watch the video to know how one can repair corrupt Exchange OST file effortlessly and convert OST emails to MS Outlook PST file format by using Kernel for OST to PST converter tool. It can convert OST to MSG, MBOX, EML to access them. It can migrate…
Through the video, you can check the migration process of Outlook PST file to PDF. Kernel for Outlook to PDF tool can convert Outlook emails with all attributes like Subject, To, From, Cc, Bcc and other folders such as Inbox, Outbox, Sent Items, Jun…

580 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