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>&nbsp</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>&nbsp</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>&nbsp</td></tr>" & _
                   "</table>"
  Else
    Response.Write "<table align=center width=600 border=0 cellspacing=0 cellspadding=0><tr>" & _
                   "<tr><td class=pagetext>&nbsp</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>&nbsp</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

This seems to be a simple matter of maintaining a counter for all the columns you wish to total up.

Before your loop:
Dim nReportTotal : nReportTotal = 0
Dim nUnfoundTotal : nUnfoundTotal = 0
Dim nActualTotal : nActualTotal = 0
Dim nArrestTotal : nArrestTotal = 0
Dim nUnder18Total : nUnder18Total = 0

Inside the loop:
nReportTotal = nReportTotal + CInt(strReport)
nUnfoundTotal = nUnfoundTotal + CInt(strUnfound)
nActualTotal = nActualTotal + CInt(strActual)
nArrestTotal = nArrestTotal + CInt(strArrest)
nUnder18Total = nUnder18Total + CInt(strUnder18)

Now you can show your column totals.

Peter
------------------------------------------------------
Work smarter, not harder.

Thanks Peter, it worked like a charm! I'd rename one of my kids after you, but since they are all girls, they may not appreciate being called Peter!
:o)

Chris