Record Additions

[Corneliu]

Can someone help me with this, please.
What i want to do is find all the records that have the same category, than add the time spent on that category, than spit out the next category. I have this, but when it lists the records, it does not add them together, the ones that are the same, it just lists them all.
What is does now:

Category Moves 60 hrs
Category Moves 30 Hrs
Category Email 45 Hrs
Category Email 60 Hrs

What I want is this:
Category Moves 90 Hrs
Category Email 105 Hrs

Here is what i got so far:
SQL Statement is based on week # and User ID.

<%
UserID = TRIM( Request.QueryString( &quot;UserID&quot )
WkID = TRIM( Request.QueryString( &quot;WkID&quot )

Set RS = Server.CreateObject(&quot;ADODB.Recordset&quot
SqlString = &quot;SELECT Main.Site, Main.Technician, Main.TSpent, Main.Category, Main.Date1, Main.Department, Main.DeptWork, Main.Week, Department.Department as Department_Name, Technicians.Technician as Tech_Name, Category.Category as Category_Name &quot;
SqlString = SqlString & &quot; FROM ((((Main INNER JOIN Technicians ON Main.Technician = Technicians.TechID) INNER JOIN Site ON Main.Site = Site.SiteID) INNER JOIN Department ON Main.Department = Department.DeptID) INNER JOIN Category ON Main.Category = Category.CatID) INNER JOIN Week ON Main.Week = Week.WeekID&quot;
SqlString = SqlString & &quot; WHERE Technicians.TechID = &quot; & UserID & &quot; and Week.WeekID = &quot; & WkID

SET RS = objConn.Execute( sqlString )

%>

The actual output:

<%
While Not RS.EOF
OldCatName = RS(&quot;Category&quot
Spent = RS(&quot;TSpent&quot
Total = 0


%>
<table width=&quot;100%&quot; border=&quot;1&quot; cellpadding=&quot;0&quot; cellspacing=&quot;2&quot; bordercolor=&quot;#000000&quot; class=&quot;Listing&quot;>
<!--DWLayoutTable-->
<tr>
<td width=&quot;204&quot; bgcolor=&quot;<%=bgColor%>&quot;><div align=&quot;left&quot;><em>Category</em></div></td>
<td width=&quot;487&quot; bgcolor=&quot;<%=bgColor%>&quot;><div align=&quot;center&quot;><em><font color=&quot;#000000&quot; size=&quot;2&quot;><strong><%=RS(&quot;Category_Name&quot%></strong>
</font></em></div></td>
<td width=&quot;287&quot; bgcolor=&quot;<%=bgColor%>&quot;><div align=&quot;right&quot;><em><font color=&quot;#000000&quot; size=&quot;2&quot;>
<%
IF Not RS.EOF then 'IF THERE IS STILL SOMETHING IN THE RECORDSET
compstr = StrComp(RS(&quot;Category&quot,OldCatName,1) ' COMPARE Categories
If compstr = 0 then 'IF THE UNIT NAMES MATCH
Total = Spent + Total
RS.MoveNext
else
OldCatName = RS(&quot;Category&quot
end if
end if
%>
<%Response.Write Total %> Hr(s)</font></em></div></td>
</tr>
</table>
<tr>
<td width=&quot;15%&quot; nowrap>
<div align=&quot;RIGHT&quot;></div></td>
<td width=&quot;5%&quot;>
<div align=&quot;CENTER&quot;> </div></td>
</tr>
<%
'ALLOWS SHOWING OF NEXT RECORD IF UNIT DOES NOT HAVE ANY MORE VALUES.
IF Not RS.EOF then
compstr = StrComp(RS(&quot;Category&quot,OldCatName,1)
If compstr <> 0 then
OldCatName = RS(&quot;Category&quot
end if
end if

Wend
%>

Any help I would aprpeciate it very much.

 

[Corneliu]

Anyone can help me with this please?