adding totals to the bottom of a dynamic table

[realtammie]

Hi,
I got the following code from a post in these forums and the solution was provided by Bob Burrows.

<%
dim cn, rs
set cn = Server.CreateObject("ADODB.Connection")
set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation=adUseClient
cn.Open "provider=microsoft.jet.oledb.4.0;data source=" & _
server.MapPath("dbname.mdb")
cn.TransactionsCrosstab rs
set rs.ActiveConnection=nothing
cn.Close:set cn=nothing
dim fld,i,val
%>
<table border="1" cellspacing="0"><tr>
<%
for each fld in rs.Fields
Response.Write "<th>" & fld.name & "</th>"
next
Response.Write "</tr>"
do until rs.EOF
Response.Write "<tr>"
for i=0 to rs.Fields.count - 1
val=rs(i).Value & ""
if len(val) = 0 then val="&nbsp;"
Response.Write "<td>" & val & "</td>"
next
Response.Write "</tr>"
rs.MoveNext
loop
rs.Close:set rs=nothing
%>
</table>

I have modified the code slightly to get it to work in my own enviroment however, I would like to know if there is a way I could display column totals at the bottom of the table

Regards

Paul

[jeffstl]

This is odd since you are writing out your results by looping through columns. More typically you will see results written out by looping through rows. You are looping through rows, and within that through columns. Again just pointing out that this is odd so the following is not usually how this is done either.

Inside the loop where you are setting val put this Select case, then below all your loops and table prints write the accumulations out.

dim MyAccumulationField1,MyAccumulationField1
Select Case i
     Case 1
          MyAccumulationField1 = MyAccumulationField1 + rs(i).Value
     Case 2
	  MyAccumulationField2 = MyAccumulationField2 + rs(i).Value

End Select

'then later on outside the loops
Response.Write "Field 1 Total : " & MyAccumulationField1
Response.Write "Field 2 Total : " & MyAccumulationField2

[jeffstl]

By the way. The best way to do this is with SQL probably. If you are getting your data rows with SQL you can select out accumulated totals instead of doing it in code. Like this:


select sum(field1) as Field1Total, Field2, Field3 from MyTable