Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    New Coder
    Join Date
    May 2006
    Posts
    92
    Thanks
    1
    Thanked 0 Times in 0 Posts

    No Clue - Show record numbers per month.

    Hi,

    I've been looking for help on this for ages with no success.. I would have thought it would be an easy thing... but it's totally baffled me.

    I've built my own blog, and it submits data to a mdb. I've included the date the post was created as one of the table fields.

    Now what I want to do is create a side menu that does this...

    Jan 07 - 6 Posts
    Feb 07 - 2 Posts
    March 07 - 0 Posts
    etc.

    Each one being a link to the posts in that particular month.
    Please could someone advise me on steps to...
    A. Display the months since 2007 but only up to the present month.
    B. Next to each month, search the records in table "blog" for the month("blogdate") and write out the total number i.e.
    Jan 07 - 7 Posts
    ..and repeat for each month.
    C. Make each month a link pulling the titles from the blogs that month.

    I'm really lost and confused, so please help if you can.
    Regards

    Here's an example of what I require..
    http://blog.case.edu/jms18/archives
    Last edited by dommy; 05-25-2007 at 09:05 PM.

  • #2
    Regular Coder
    Join Date
    Jan 2005
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts
    to display the months from beginning of year to current, try something like this:
    Code:
    curYear = year(now())
    for i = 1 to month(now())
         strSQL = "SELECT count(*) as totalPosts FROM blog WHERE month(blog_date) = '" & i & "' AND year(blog_date) = '" & curYear & "'"
         set rs = conn.execute(strSQL)
         if rs.EOF and rs.BOF then
              response.Write(monthname(i) & " " & curYear & " - 0 posts")
         else
              response.Write(monthname(i) & " " & curYear & " - ")
              response.Write("<a href=""display_page.asp?m=" & i & "&amp;y=" & curYear"">" & rs("totalPosts") & "post(s)</a>")
         end if
         rs.Close
    next
    the link would go to another page where you would pull the records from the database that match the month & year and display them.

  • #3
    New Coder
    Join Date
    May 2006
    Posts
    92
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks for the speedy reply Mehere...!

    I totally understand your reply and will give it a shot now...and will post back soon, so do check back

    P.S. Just because I found it, I thought I'd post another example..

    http://www.outsightinteractive.com/blog/archive/200702
    see the right hand side.

    Cheers

  • #4
    Senior Coder
    Join Date
    Apr 2003
    Location
    England
    Posts
    1,192
    Thanks
    5
    Thanked 13 Times in 13 Posts
    You can do this mostly in the SQL. Note that it will skip months with 0 posts.

    SELECT post_date, count(post_id) FROM posts GROUP BY MONTH(post_date)

    Something like that would do. It would be a lot more efficient but you would need to add code to insert the 0 post months (if you ever have 0 post months). You could also extend this to only count the last 12 months or whatever ir you wanted.

    I have not tried this on ms access databases, but even if the syntax is wrong for access, you should be able to do it somehow (eg datepart if month doesn't work). The key is the GROUP BY and the COUNT.

    EDIT: You can also add another group by on year and should be able to order it by month and year. To add the 0s just look for gaps when looping through. For example if you have

    2007, 05, 2
    2007, 06, 14
    <-- When you are at the start of year 2007 month 8, you can notice that the last one you did was year 2007 month 06 and print out the missing months.
    2007, 08, 3

    There may be an easier way of adding the missing months (if you want them that is)

    Again I'd like to point out that this single SQL query is a LOT more efficient than looping through and performing multiple queries. You should always do as much as possible in SQL if you can.
    Last edited by ghell; 05-26-2007 at 12:57 AM.

  • #5
    New Coder
    Join Date
    May 2006
    Posts
    92
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hey ghell,

    Many thanks for the reply...
    I will not be able to test either now.. but I will post my findings/success tomorrow. Many many thanks for your time!



    EDIT: I get the following problem:
    You tried to execute a query that does not include the specified expression 'post_date' as part of an aggregate function.

    I changed my fields in my db to match what u used in the sql expression so I don't get what's wrong.?
    Last edited by dommy; 05-26-2007 at 01:16 AM.

  • #6
    Senior Coder
    Join Date
    Apr 2003
    Location
    England
    Posts
    1,192
    Thanks
    5
    Thanked 13 Times in 13 Posts
    Ok, you can only return fields that are actually in the group by. I changed my SQL half way through and forgot to change that part.

    Try just doing
    SELECT *, COUNT(*) FROM ...
    if you are not sure.

    This should come out something like this (again, not tested)

    SELECT *, COUNT(*) FROM posts GROUP BY MONTH(post_date), YEAR(post_date) ORDER BY MONTH(post_date), YEAR(post_date)

    You might have to switch the year and month around.

    In most cases if you can't pull out something because it is not part of the group, you can just wrap it in an aggregate function like min or first (if you know it is always the same for example, getting the minimum of 1,1,1,1,1,1 is always going to be 1) but here you should be able to pull the year, month and count out because count is aggregate and month and year are in the group by. However, I haven't tested so I may be making a mistake somewhere.

    EDIT: Don't forget to change the table and field names to match your table.
    Last edited by ghell; 05-26-2007 at 02:18 AM.

  • #7
    Regular Coder
    Join Date
    Jan 2005
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts
    any item in your select that is not an aggregate function must be included in your group by.
    Code:
    SELECT MONTH(post_date), YEAR(post_date), COUNT(*) FROM posts GROUP BY MONTH(post_date), YEAR(post_date) 
    ORDER BY MONTH(post_date), YEAR(post_date)

  • #8
    New Coder
    Join Date
    May 2006
    Posts
    92
    Thanks
    1
    Thanked 0 Times in 0 Posts
    That works perfectly Mehere! You both have helped me very much - I'm starting to understand the count procedure - very useful addition! MUCH better than working back and forth through a database.

    Set objRS = objConn.Execute ("SELECT MONTH(BlogDate), YEAR(BlogDate), COUNT(*) FROM blog GROUP BY MONTH(BlogDate), YEAR(BlogDate) ORDER BY MONTH(BlogDate), YEAR(BlogDate)")
    One last favour if you will, as I'm still trying to get used to writing out the response.write codes in relation to the sql statements...

    What do I then call to get my months and the totals?
    i.e. write out the..

    Jan - 5 Post(s)
    Feb - 3 Post(s)

    I know that at the moment the month is a number so this will need to be changed to match a month i.e. 1=jan etc
    CurrDate = Now()
    CurrMonthID = Month(CurrDate)
    CurrMonthName = MonthName(CurrMonthID)
    This gives the current month.. now to edit to show each month?

    Sorry if this seems obvious, but with an example, I know how to perform this in the future.

    Many thanks again guys!

    UPDATE:

    Just to show I'm trying - this as you know doesn't change the month number to a word yet i.e. Jan.

    Set objRS = objConn.Execute ("SELECT MONTH(BlogDate), YEAR(BlogDate), COUNT(*) AS TotalPosts FROM blog GROUP BY MONTH(BlogDate), YEAR(BlogDate) ORDER BY MONTH(BlogDate), YEAR(BlogDate)")
    BlogMonth = objRS("MONTH(BlogDate)")
    BlogYear = objRS("YEAR(BlogDate)")
    BlogCount = objRS("TotalPosts")
    if objRS.EOF and objRS.BOF then
    response.Write(BlogMonth & " " & BlogYear & ": " & "0 Posts")
    else
    response.Write(BlogMonth & " " & BlogYear & ": " & BlogCount & "" & ": ")
    response.Write "<a href=""blog.asp?m=" & BlogMonth & "&amp;y=" & BlogYear &">" & BlogCount & " Post(s)</a>"
    end if
    next
    I've added in AS TotalPosts (is this correct? - It's not a field in my database, does it need to be?)

    But this doesn't work... I think that's because in the sql we're already ordering each month/year, and so I don't need to loop the response.write code for each month?? If so I'm not sure how I'm meant to get my results onto the page.
    P.S. When I run this it doesn't like the NEXT.

    Thanks again.
    Last edited by dommy; 05-26-2007 at 08:31 PM.

  • #9
    Regular Coder
    Join Date
    Jan 2005
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts
    give this a shot. this should list all months, including those with 0 between the beginning of the current year and now
    Code:
    curYear = year(now())
    pastYear = curYear-1
    curMonth = month(now())
    
    if curMonth <> "1" then
            for i = 2 to curMonth
    	        curSelect = curSelect & " UNION SELECT " & i
            next
    end if
    
    strSQL = "SELECT month(dateadd(month,months.m,'" & pastYear & "1201')) as month, count(blogdate) as totCount " 	& _
    	 "FROM (SELECT m = 1 " & curSelect & ") months "							& _
    	 "LEFT OUTER JOIN blogs b ON b.blogdate >= dateadd(month,months.m-1,'" & pastYear & "1201'") "		& _
    	 "AND b.blogdate < dateadd(month,months.m,'" & pastYear & "1201') "					& _
    	 "GROUP BY dateadd(month,months.m,'" & pastYear & "1201')"
    Set objRS = objConn.Execute (strSQL)
    
    do while not objRS.EOF
    	if objRS("totCount") = 0 then
    		response.Write(monthname(objRS("month")) & " " & curYear &": " & objRS("totCount") & " Post(s)<br />") 
    	else
    		response.Write("<a href=""blog.asp?m=" & objRS("month") & "&amp;y=" & curYear & ">"
    		response.Write(monthname(objRS("month")) & " " & curYear &": " & objRS("totCount") & " Post(s)</a><br />"
    	end if
    	objRS.MoveNext
    loop
    i'm not sure which database you are using but this works on SQL Server, not sure it will work in Access.
    Last edited by mehere; 05-27-2007 at 04:37 PM.

  • #10
    New Coder
    Join Date
    May 2006
    Posts
    92
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Mehere,

    When I run the code, it complains, Item cannot be found in the collection corresponding to the requested name or ordinal.
    the line it refers to is..
    curSelect = curSelect & " UNION SELECT " & i
    Also there seems to be something wrong with this line when looking at the code in dreamweaver - it's greying out the last part...

    "AND b.BlogDate < dateadd(month,months.m,'" & pastYear & "1201')" & _
    Also, Can you just clarify what dateadd is?

    P.S. I am indeed using Access.
    Again down on my knees thanking you!!
    Last edited by dommy; 05-28-2007 at 02:56 AM.

  • #11
    Senior Coder
    Join Date
    Apr 2003
    Location
    England
    Posts
    1,192
    Thanks
    5
    Thanked 13 Times in 13 Posts
    union is the set operator for 2 query results. You probably don't want it as it is very rarely useful. When you union they have to be 2 queries, and both queries have to have the same result format (for example an int then a varchar(20) then a datetime).

    It looks like you are using this badly anyway from "union select" & i. Normally it would be something like "select id, name from sometable UNION select age, address from someothertable" I couldn't thnk of a non-trivial example due to union being pretty useless for most things (I suppose you could use it if you were creating 1 result set out of multiple in a stored procedure loop or something). Anyway, you can see that it just goes between 2 queries with the same result signature. It joins the 2 results together vertically, theoretically removing duplicates (but that depends on the DBMS)

    In this case it seems to just be used to get a result set like this:
    col 1
    -----
    2
    3
    4
    5
    (assuming 5 is the current month)

    which seems like a very weird way to do it to me.


    dateadd adds to a date and returns the new date. For example dateadd 4 days to the 2007-05-08 (8th may 2007) and you get 2007-05-12

  • #12
    New Coder
    Join Date
    May 2006
    Posts
    92
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Ok.. Lol two experts are disagreeing - argh this doesn't help Well, I knew this was a hard thing to achieve being a newbie. Anyways, ghell, using the sql code above, how would you go about doing this then?

    Thanks

  • #13
    Regular Coder
    Join Date
    Jan 2005
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I just tried testing it in Access and it doesn't seem to work. I agree with ghell it is a wierd way of doing it, but it did indeed return months where there were no records, so it was the easiest way to do it.

    and regarding your error regarding this line:
    "AND b.BlogDate < dateadd(month,months.m,'" & pastYear & "1201')" & _
    has to do with the line before it having 1 too many quotes.
    "LEFT OUTER JOIN blogs b ON b.blogdate >= dateadd(month,months.m-1,'" & pastYear & "1201') " & _

    Anyway, since you're using Access, we need to go another direction:
    First: Create a table called months, with a column called month and insert rows with the months 1 through 12
    Second: Change your query & Code to this:
    Code:
    curYear = year(now())
    curMonth = Month(now())
    
    strSQL = "TRANSFORM First(Total) AS totCount " & _
    		 "SELECT Format([Month],""00"") AS thisMonth " & _
    		 "FROM (" & _
    		 "SELECT A.[Year], A.[Month], SUM(A.Total) as Total " & _
    		 "FROM (" & _
    		 "SELECT Year(blogdate) as [Year], Month(blogdate) as [Month], COUNT(*) as [Total] " & _
    		 "FROM blogs GROUP BY Year(blogdate), Month(blogdate) " & _
    		 "UNION " & _
    		 "SELECT " & _
    		 "(SELECT max(year(blogdate)) FROM blogs WHERE year(blogdate)=" & curYear & ") as [Year]," & _
    		 "[Month], Null FROM Months " & _
    		 ") A " & _
    		 "GROUP BY A.[Year], A.[Month]) " & _
    		 "GROUP BY Format(Month,""00"") PIVOT [Year];"
    Set objRS = objConn.Execute (strSQL)
    
    If objRS.EOF And objRS.BOF Then
    	Response.Write("NO RECORDS")
    Else
    	Do while not objRS.EOF
    		thisMonth = objRS(0)
    		thisTotal = objRS(1)
    		if CInt(objRS(0)) <= CInt(curMonth) Then
    			If isNull(objRS(1)) Then
    				response.Write(left(monthname(objRS(0)),3) & " " & curYear &": 0 Post(s)<br />") 
    			Else
    				response.Write("<a href=""blog.asp?m=" & thisMonth & "&amp;y=" & curYear & """>" & left(monthname(thisMonth),3) & " " & curYear &": " & thisTotal & " Post(s)</a><br />")
    			End If
    		End If
    		objRS.MoveNext
    	Loop
    End If
    let's see how this works for you.
    Last edited by mehere; 05-28-2007 at 07:17 PM.

  • #14
    New Coder
    Join Date
    May 2006
    Posts
    92
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Mehere,

    I don't think I can use UNION... It can't find the second table, the Months table... it's there 100% - here's how I setup that table...

    Link to image

    D.

  • #15
    Regular Coder
    Join Date
    Jan 2005
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts
    i've put it into an access database to test it out and placed it on an ASP page to make sure the code was correct. it all works from this end.

    try running this directly in your access database and see what happens:
    Code:
    TRANSFORM First(Total) AS totCount 
    SELECT Format([Month],"00") AS thisMonth 
    FROM 
    (
    SELECT A.[Year], A.[Month], SUM(A.Total) as Total 
    FROM 
    (
    SELECT Year(blogdate) as [Year], Month(blogdate) as [Month], COUNT(*) as [Total] 
    FROM blogs
    GROUP BY Year(blogdate), Month(blogdate) 
    UNION 
    SELECT 
    (
    SELECT max(year(blogdate)) FROM blogs WHERE year(blogdate)=2007) as [Year],
    [Month], 
    Null 
    FROM Months 
    ) A 
    GROUP BY A.[Year], A.[Month]
    ) 
    GROUP BY Format(Month,"00") PIVOT [Year];
    just noticed your table name is blog not blogs ... did you make that change?

    try testing it from the files i included in the zip file (asp page/access database)
    Attached Files Attached Files
    Last edited by mehere; 05-29-2007 at 12:43 AM. Reason: added zip file


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •