How to a Sum from query search results

jennypretty · June 22nd, 2015, 12:00 PM

Hello,
I want to SUM from a query search result. I have a table called Students. I created a Search form to list students with scores from Jan or Feb, by date range. The results are different (two rows, four rows, so on) based on how many months I search. I created a Sum row at the bottom to count the total from that column for Jan or Feb. However, it doesn't work at all.
Can anyone help?

Here is my code:
<table><tr>
<td>Student Name</td><td>Jan</td><td>Feb</td>
</tr>
<tr><td></td>
</tr>
<tr>
<td><%=oRs("Name")%></td>
<td <%=sRowStyle%>><%=oRs("Jan")%></td>
<td <%=sRowStyle%>><%=oRs("Feb")%></td>
</tr>

<tr><td colspan="3">
<%
total = total + CInt(oRs("Jan"))
oRs.MoveNext
WEND %>
</td>
<tr>
</tr>
</table>

mat41 · June 24th, 2015, 01:06 AM

Your code is way off. You do a .moveNext but dont have a loop there. IN futire saying doesnt work at all is not helpful. Be more detailed. What doesnt work, whats the error, whats the expected bahaviour and whats the actual behaviour?

Replace all your code with this

Code:

<table>
 <tr>
  <td>Student Name</td>
  <td>Jan</td>
  <td>Feb</td>
 </tr>
 <tr>
  <td colspan="3"></td>
 </tr>
<% if not oRs.eof then
      do until oRs.eof %>
         <tr>
          <td><%=oRs("Name")%></td>
          <td <%=sRowStyle%>><%=oRs("Jan")%></td>
          <td <%=sRowStyle%>><%=oRs("Feb")%></td>
         </tr>
<%       total = total + CInt(oRs("Jan"))
         ors.moveNext
	   loop %>
       <tr>
        <td colspan="3"><%= total %></td>
       </tr>        
<% else
       response.write "<tr><td colspan='3'>No data found</td></tr>"
   end if %>
</table>

jennypretty · June 24th, 2015, 08:41 AM

It works perfect. Thanks very much.