Notices

Great community. Great ideas.

Welcome to ASP/Free, a community dedicated to helping beginners and professionals alike in improving their knowledge of Microsoft's development and administration technologies. Sign up today to gain access to the combined insight of tens of thousands of members.

Thread: Count Records by ID Per Month

Results 1 to 6 of 6
Share This Thread →
  1. #1
    duval is offline Contributing User
    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
    bigmike1212's Avatar
    bigmike1212 is offline 0x800A0C93
    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

  3. #3
    gk53's Avatar
    gk53 is offline Contributing User
    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,535
    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

  4. #4
    bigmike1212's Avatar
    bigmike1212 is offline 0x800A0C93
    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.

  5. #5
    gk53's Avatar
    gk53 is offline Contributing User
    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,535
    Rep Power
    278
    Sorry, I forgot it is access...

  6. #6
    bigmike1212's Avatar
    bigmike1212 is offline 0x800A0C93
    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.

Share This Thread →

Become Part of This Conversation

Join NowFor Free!

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

ASP Free Advertisers and Affiliates