If anyone could help, I'd be greatly indebted.. Again :o) ...
I have a query that runs on a law enforcement website that queries based on charge type and year. The results will then display totals for each suboffense of that particular charge type. Everything displays fine except for one thing. I need to sum all totals in each field. However, some charges contain more suboffenses than others. One charge may contain 3 sub offenses where another charge may contain 8 sub offenses. I know this is confusing, but I have the current working for viewing at
http://www.harrisoncountysheriff.com/statistics/
Here is the code that I currently have:
<%
Dim objComm, objRS, strOff, strDate
strOFF = Request.Form("offense")
strDate = Request.Form("offyear")
Set objComm = Server.CreateObject("ADODB.Command")
objComm.ActiveConnection = strConnect
objComm.Commandtext = "SELECT * FROM tbl_offenses WHERE offense = '" & strOFF & "' " & _
"AND offyear = '" & strDate & "' ORDER BY suboff ASC"
objComm.CommandType = adCmdText
Set objRS = objComm.Execute
Set objComm = Nothing
If objRS.EOF Then
Response.Write "<table align=center width=450 border=0 cellspacing=0 cellpadding=0>" & _
"<tr><td class=text> </td></tr>" & _
"<tr><td class=pagetext align=center><b>Statistics for " & strDate & " will be added soon<br>" & _
"Please check back later.</b></td></tr>" & _
"<tr><td class=pagetext> </td></tr>" & _
"<tr><td class=pagetext align=center bgcolor=dddddd><a href='stats.asp'>Search Again</a> | " & _
"<a href='index.html'>Statistics Main</a></td></tr>" & _
"<tr><td class=text> </td></tr>" & _
"</table>"
Else
Response.Write "<table align=center width=600 border=0 cellspacing=0 cellspadding=0><tr>" & _
"<tr><td class=pagetext> </td></tr>" & _
"<td align=left bgcolor=ffcc00 colspan=6 class=pagetext>" & _
"<b>" & strDate & "</b> statistics for <b>" & objRS("offense") & "</b></td></tr>" & _
"<tr><td bgcolor=333333 colspan=6></td></tr>" & _
"<tr><td class=pagetext align=left colspan=5>Below are the sub-offenses and statistics associated with " & _
objRS("offense") & ".</td></tr>" & _
"<tr><td class=pagetext> </td></tr>" & _
"<tr><td align=left bgcolor=cccccc width=255 class=statheader></td>" & _
"<td class=statheader align=center width=70 bgcolor=cccccc>Reported</td>" & _
"<td align=center class=statheader width=70 bgcolor=cccccc>Unfounded</td>" & _
"<td class=statheader align=center width=70 bgcolor=cccccc>Actual</td>" & _
"<td align=center class=statheader width=70 bgcolor=cccccc>Arrested</td>" & _
"<td class=statheader align=center width=70 bgcolor=cccccc>Under 18</td></tr>"
Do While Not objRS.EOF
Dim strReport, strUnfound, strActual, strArrest, strUnder18, strSuboff
strReport = objRS("reported")
strUnfound = objRS("unfounded")
strActual = objRS("actual")
strArrest = objRS("arrestable")
strUnder18 = objRS("under18")
strSubOff = objRS("suboff")
Response.Write "<tr><td colspan=6 class=pagetext><img src='../menu/shim.gif' height=2 width=10></td></tr>" & _
"<tr><td align=left width=255 class=statvalue bgcolor=cccccc><b>" & strSubOff & "</b></td>" & _
"<td class=statvalue align=center width=70>" & strReport & "</td>" & _
"<td align=center class=statvalue width=70>" & strUnfound & "</td>" & _
"<td class=statvalue align=center width=70>" & strActual & "</td>" & _
"<td class=statvalue align=center width=70>" & strArrest & "</td>" & _
"<td align=center class=statvalue width=70>" & strUnder18 & "</td></tr>" & _
"<tr><td colspan=6 class=pagetext bgcolor=cccccc><img src='../menu/shim.gif'" & _
" height=2 width=10></td></tr>" & _
"<tr><td class=pagetext colspan=6><img src='../menu/shim.gif' height=2 width=10></td></tr>" & _
objRS.MoveNext
Loop
Response.Write "<tr><td class=pagetext colspan=6 bgcolor=333333></td></tr>" & _
"<tr><td class=pagetext align=center bgcolor=dddddd colspan=6><a href='stats.asp'>" & _
"Search Again</a> | <a href='index.html'>Statistics Main</a></td></tr>" & _
"</table>"
End If
objRS.Close
Set objRS = Nothing
%>
I've thought of counting records but I'm not quite sure as to how to employ it correctly. I'll be happy to try and clear up any confusion as need be in a reply.
Thanks!
Chris