bevssi 0 Newbie Poster

I'm new at this and still get really stuck so I've probably overworked this till I can't see how simple it is. I just need a report that shows which churches are entering monthly reports. Example:

Desired Report:

Church Jan feb mar april etc.

churchA X N Y N
churchB Y Y Y Y
churchC N Y Y N


OK here's what I have. The first month works perfect, but the second month is wrong and I don't know how to fix for records not entered yet for months August-December.

<th class=td1 colspan="2">&nbsp;Church&nbsp;</th>
<th class=td1>&nbsp;Jan&nbsp;</th>
<th class=td1>&nbsp;Feb&nbsp;</th>
<th class=td1>&nbsp;Mar&nbsp;</th>
<th class=td1>&nbsp;Apr&nbsp;</th>
<th class=td1>&nbsp;May&nbsp;</th>
<th class=td1>&nbsp;Jun&nbsp;</th>
<th class=td1>&nbsp;Jul&nbsp;</th>
<th class=td1>&nbsp;Aug&nbsp;</th>
<th class=td1>&nbsp;Sep&nbsp;</th>
<th class=td1>&nbsp;Oct&nbsp;</th>
<th class=td1>&nbsp;Nov&nbsp;</th>
<th class=td1>&nbsp;Dec&nbsp;</th>
</tr>
<%
strSQL="select mem.churchid, ch.churches_name FROM members mem LEFT OUTER JOIN ochurches ch ON ch.churches_id=mem.churchid and mem.year=2011 WHERE mem.district=21 ORDER BY ch.churches_name_full"

Set rs = Conn.Execute(strSQL)

if not rs.eof then
i=0
do while not rs.eof
i=i+1

churchid=rs("churchid")
church=rs("churches_name")

strSQL="Select ev.month, ev.report_date from evandata ev where ev.churchid="& churchid &" and ev.year=2011 and ev.month IS NOT NULL ORDER BY ev.month"

Set rs1 = Conn.Execute(strSQL)

If not rs1.eof Then

rmonth=rs1("month")
rdate=rs1("report_date")


If rmonth = 1 Then
jan=True
Else jan=False
End If

If jan=True Then
jan=("&nbsp;Y&nbsp;")
Else
jan=("&nbsp;N&nbsp;")
End If

If rmonth = 2 Then
febrec=True
Else febrec=False
End If

If febrec=True Then
febrec=("&nbsp;Y&nbsp;")
Else
febrec=("&nbsp;N&nbsp;")
End If

response.write "<td colspan=2 class=td3>" & church & "</td><td align=center>" & janrec & "</td><td align=center>" & febrec & "</td></tr>"

End If

rs1.Close
Set rs1=Nothing

rs.movenext

loop


%>
<%End If
%>

<%
rs.close
set rs=nothing

%>
</td></tr></tr></table>
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.