#1
  1. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Apr 2009
    Posts
    82
    Rep Power
    9

    Count Records by ID Per Month


    I have a working sql statement to count the total number of contacts by student ID but need to get the contacts for each month to display in the same page. The database is Access

    Right now it just displays:
    sn_student_id - countofcontacts

    I need to be able to display:
    sn_student_id - countofcontacts-jne2012 - countofcontacts-jly2012 - countofcontacts-jly2012 ETC

    Code:
    <%
    Dim Recordset1__vardate1
    Recordset1__vardate1 = "1/1/2011"
    If (emptyvalue <> "") Then 
      Recordset1__vardate1 = emptyvalue
    End If
    %>
    <%
    Dim Recordset1__vardate2
    Recordset1__vardate2 = "12/1/2012"
    If (emptyvalue <> "") Then 
      Recordset1__vardate2 = emptyvalue
    End If
    %>
    <%
    Dim Recordset1
    Dim Recordset1_cmd
    Dim Recordset1_numRows
    
    Set Recordset1_cmd = Server.CreateObject ("ADODB.Command")
    Recordset1_cmd.ActiveConnection = MM_con_students12_13_STRING
    Recordset1_cmd.CommandText = "SELECT DISTINCT sn_student_id, COUNT (sn_student_id) AS C FROM sn_contacts WHERE sn_date BETWEEN ? AND ?  AND sn_prefix = 'CCG' Group BY sn_student_id " 
    Recordset1_cmd.Prepared = true
    Recordset1_cmd.Parameters.Append Recordset1_cmd.CreateParameter("param1", 135, 1, -1, Recordset1__vardate1) ' adDBTimeStamp
    Recordset1_cmd.Parameters.Append Recordset1_cmd.CreateParameter("param2", 135, 1, -1, Recordset1__vardate2) ' adDBTimeStamp
    
    Set Recordset1 = Recordset1_cmd.Execute
    Recordset1_numRows = 0
    %>
  2. #2
  3. 0x800A0C93
    ASP Super Hero (2500 - 2999 posts)

    Join Date
    Jan 2009
    Location
    Ridin' through this world...
    Posts
    2,975
    Rep Power
    1310
    Given data like:
    table contacts
    Code:
    studentID	contactdate
    1		1/1/2011
    1		1/2/2011
    1		2/5/2012
    1		3/5/2012
    2		1/1/2011
    2		4/5/2012
    2		4/6/2012
    2		4/7/2012
    2		12/12/2012
    3		6/5/2012
    3		6/6/2012
    3		7/6/2012
    3		7/7/2012
    3		8/8/2012
    3		8/9/2012
    3		12/14/2013
    4		1/1/2011
    4		2/1/2011
    4		3/1/2011
    4		4/1/2011
    a query like:
    Code:
    Select   studentid, count(studentid) as studentcount,  monthname(month(contactdate), true) as contactmonth
    FROM      contacts 
    WHERE     year(contactdate) = '2012' 
    GROUP BY  month(contactdate), studentid
    order by 1
    should yield a resultset like:
    Code:
    studentid	studentcount	contactmonth
    1		1		Feb
    1		1		Mar
    2		3		Apr
    2		1		Dec
    3		2		Jun
    3		2		Jul
    3		2		Aug
    which you ought to be able to write like you want using code.
    Brinkster- free development account. Not affiliated, but I sure like free. Brinkster.com
  4. #3
  5. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,581
    Rep Power
    278
    this one give you what you need
    Code:
    create table #T
    (
    studentID int,
    contactdate datetime
    )
    
    insert into #T (studentID, contactdate)
    select
    1,'1/1/2011'
    union
    select
    1,'1/2/2011'
    union
    select
    1,'2/5/2012'
    union
    select
    1,'3/5/2012'
    union
    select
    2,'1/1/2011'
    union
    select
    2,'4/5/2012'
    union
    select
    2,'4/6/2012'
    union
    select
    2,'4/7/2012'
    union
    select
    2,'12/12/2012'
    union
    select
    3,'6/5/2012'
    union
    select
    3,'6/6/2012'
    union
    select
    3,'7/6/2012'
    union
    select
    3,'7/7/2012'
    union
    select
    3,'8/8/2012'
    union
    select
    3,'8/9/2012'
    union
    select
    3,'12/14/2013'
    union
    select
    4,'1/1/2011'
    union
    select
    4,'2/1/2011'
    union
    select
    4,'3/1/2011'
    union
    select
    4,'4/1/2011'
    
    select * from #T
    
    select distinct studentID, 
    COUNT(studentID) as studentcount,  
    DATENAME(month, contactdate) as contactmonth, 
    DATEPART(year, contactdate) as contactyear 
    
    from #T
    where DATEPART(year, contactdate)=2012
    group by studentID,
    DATENAME(month, contactdate) , 
    DATEPART(year, contactdate) 
    order by studentID, DATENAME(month, contactdate)
    
    drop table #T
    GK
    __________________________________________________ _____
    if you found this post is useful click Give Rep button (bottom side on this reply ) and agree
  6. #4
  7. 0x800A0C93
    ASP Super Hero (2500 - 2999 posts)

    Join Date
    Jan 2009
    Location
    Ridin' through this world...
    Posts
    2,975
    Rep Power
    1310
    GK,

    DATENAME is not an Access function.
    The syntax for DATEPART is wrong.

    Using this corrected code:

    Code:
    select distinct studentID, 
    COUNT(studentID) as studentcount,  
    monthNAME(month(contactdate), true) as contactmonth, 
    DATEPART("yyyy", contactdate) as contactyear 
    
    from contacts
    where DATEPART("yyyy", contactdate)=2012
    group by studentID,
    monthNAME(month(contactdate), true) , 
    DATEPART("yyyy", contactdate) 
    order by studentID, monthNAME(month(contactdate), true)
    gives the same results as my code.
  8. #5
  9. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,581
    Rep Power
    278
    Sorry, I forgot it is access...
  10. #6
  11. 0x800A0C93
    ASP Super Hero (2500 - 2999 posts)

    Join Date
    Jan 2009
    Location
    Ridin' through this world...
    Posts
    2,975
    Rep Power
    1310
    No big deal. It was an academic exercixe anyway. OP is long gone, won't implement this solution, and it isn't what they want in any event. I just answered the question because I didn't know how to do it and wanted to figure it out.

Similar Threads

  1. Count records
    By kill_switch413 in forum ASP Development
    Replies: 8
    Last Post: June 8th, 2011, 01:09 PM
  2. Replies: 10
    Last Post: July 27th, 2009, 02:18 AM
  3. Count from a combo box per month
    By pld60 in forum Microsoft Access Help
    Replies: 1
    Last Post: July 22nd, 2009, 07:44 PM
  4. Asp count records
    By catbollu in forum ASP Development
    Replies: 3
    Last Post: December 6th, 2004, 03:27 PM
  5. count records per month, help needed
    By iscode in forum ASP Development
    Replies: 3
    Last Post: May 20th, 2004, 06:52 AM

IMN logo majestic logo threadwatch logo seochat tools logo