counting DB entry values (not rows)

[Lord Merlin]

How do I add up the values of the data from all of the rows returned? i.e -
I have a DB, with 148 rows of client info, and I want to addup all the
scores. Each entry's score differes. Here is the code I have sofar:

SQL = "select * from company_scores order by company_name"
Set objRS = Server.CreateOb ject("ADODB.Rec ordset")

objRS.CursorLoc ation = adUseServer
objRS.CursorTyp e = adOpenStatic
objRS.ActiveCon nection = objConn
objRS.open SQL, objConn

i=0
icount = 0
Do until objRS.EOF
company_name = objRS("company_ name")
'currentday = day(objRS("sDat e"))
'currentmonth = month(objRS("sD ate"))
'currentyear = year(objRS("sDa te"))

'Select Case currentmonth
'Case 1 iMonth = "January"
'Case 2 iMonth = "February"
'Case 3 iMonth = "March"
'Case 4 iMonth = "April"
'Case 5 iMonth = "May"
'Case 6 iMonth = "June"
'Case 7 iMonth = "July"
'Case 8 iMonth = "August"
'Case 9 iMonth = "September"
'Case 10 iMonth = "October"
'Case 11 iMonth = "November"
'Case 12 iMonth = "December"
'End Select

'thecurrentdate = Cstr(currentday ) & " " & iMonth & " " &
Cstr(currentyea r)
on error resume next

iCompliments = objRS("complime nts")
iComplaints = objRS("complain ts")
If iCompliments < 1 Then iCompliments = 0
If iComplaints < 1 Then iComplaints = 0
If isnull(iComplim ents) Then iCompliments = 0
If isnull(iComplai nts) Then iComplaints = 0
tCompliments = iCompliments + iCompliments
tComplaints = iComplaints + iComplaints

If isnull(objRS("s core")) then
myendscore = "No data yet"
Else
myendscore = objRS("score") & "/" & 10
End If

if i=0 then
tdcolor="#CEC6D 6"
i=1
Else
tdcolor="#BCB1C 7"
i=0
End if
%>
<TR>
<TD COLSPAN="5"><IM G SRC="images/blank.gif" WIDTH=2
HEIGHT=2></TD>
</TR>
<TR>
<TD WIDTH="862" VALIGN="top" BGCOLOR="<%=tdc olor%>">
<TABLE BORDER="0" CELLSPACING="0" CELLPADDING="0" >
<TR>
<TD WIDTH="7"><IMG SRC="images/blank.gif" WIDTH="7"
HEIGHT="3"></TD>
<TD VALIGN="top" ALIGN="left" CLASS="cwc">
<%company_name= trim(company_na me)

%> <B><SPAN CLASS="link1">< A HREF =
"cwcsearch.asp? searchby=1000&l ocation=&city=& Country=0&suppl ier=<%=company_ n
ame%>&keyword=& industry=non&al ias=&cwc=Y" TITLE="Click here to see recent
comments">
<%=company_name %></A></SPAN></B> </TD>
</TR>
</TABLE></TD>
<TD WIDTH="45" ALIGN="RIGHT" VALIGN="middle"
BGCOLOR="<%=tdc olor%>" CLASS="cwc"><%= iCompliments%></TD>
<TD WIDTH="45" ALIGN="RIGHT" VALIGN="middle"
BGCOLOR="<%=tdc olor%>" CLASS="cwc"><%= iComplaints%></TD>
<TD WIDTH="7" BGCOLOR="<%=tdc olor%>" CLASS="cwc"><IM G
SRC="images/blank.gif" WIDTH="7" HEIGHT="3"></TD>
<%
icount = icount +1
ObjRS.Movenext
Loop

I now want to display the total amount of Compliments and the total amount
of Complaints.As you can see, i tried the following approach, but it didn't
work out:

iCompliments = objRS("complime nts")
iComplaints = objRS("complain ts")
If iCompliments < 1 Then iCompliments = 0
If iComplaints < 1 Then iComplaints = 0
If isnull(iComplim ents) Then iCompliments = 0
If isnull(iComplai nts) Then iComplaints = 0
tCompliments = iCompliments + iCompliments
tComplaints = iComplaints + iComplaints

Thanx :)

--

Kind Regards
Rudi Ahlers
[phone]

Greater love has no one than this, that he lay down his life for his friends
(John 15:13).

[Ray at]

"Lord Merlin" <SP4M_Rudi@SP4M _Bonzai.org.za_ SP4M> wrote in message
news:Tb******** ************@is .co.za...

How do I add up the values of the data from all of the rows returned? i.e - I have a DB, with 148 rows of client info, and I want to addup all the
scores. Each entry's score differes. Here is the code I have sofar:

I think you could do this with all one a single record resultset, like:

SQL = "SELECT SUM(compliments ),SUM(complaint s) FROM company_scores"
objConn.Open <your connection string>
Set objRS = objConn.Execute (SQL)
iCompliments = objRS.Fields.It em(0).Value
iComplaints = objRS.Fields.It em(1).Value
objRS.Close : Set objRS = Nothing
objConn.Close : Set objConn = Nothing

Ray at work