Count Records by ID Per Month

[duval]

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

<%
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
%>

[bigmike1212]

Given data like:
table contacts

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:

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:

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.

[gk53]

this one give you what you need

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

[bigmike1212]

GK,

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

Using this corrected 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.

[gk53]

Sorry, I forgot it is access...

[bigmike1212]

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.