Adding Sub-totals to recordset

[PeasNCarrots]

I would like to add sub-totals within the recordset results on my asp page. I want the results to be displayed after the corresponding set of related records. Currently, my results are in a table created by the following code.

<table border="0" width="90%" cellspacing="0" cellpadding="2">
<tr>
<%
Dim I
For I = 0 To RS.Fields.Count -1
response.write "<th BGCOLOR = ""C0C0C0""><font size = ""2""><b>" & RS.Fields(I).Name & "</b></font></th>"
Next
%>
</tr>
<%
Do While Not RS.EOF
Response.Write "<tr>"
For i = 0 to RS.Fields.Count -1
Response.write "<td align=""center"" bgcolor=""#F7EFDE""><font size = ""2"">" & RS.Fields(I).Value & "</font></td>"
Next
Response.Write "</tr>"
RS.MoveNext
Loop
%>
</table>


I was thinking about running a sub-total query and then appending data from both recordsets into a new table, and then display the table's recordset? What would be the best approach?

 

[vbkris]

u could also try this, its the DO loop thats browsing thro the recordset, therefore u can add it there
Tot=0
Do While Not RS.EOF
Response.Write "<tr>"
For i = 0 to RS.Fields.Count -1
Tot=Tot+Rs("FieldThatMustBeTotaled")
Response.write "<td align=""center"" bgcolor=""#F7EFDE""><font size = ""2"">" & RS.Fields(I).Value & "</font></td>"
Next
Response.Write "</tr>"
RS.MoveNext
loop
resposne.write Tot

Known is handfull, Unknown is worldfull

 

[PeasNCarrots]

I am looking for subtotals of 4 different fields when the Employee Name changes. My data output looks like this.

Emp Name Emp# Over/Under Rd Hrs PaidHrs Date
Chris 1234 3 5 8 1/30
chris 1234 4 6 8 2/1
Mark 4565 4 4 4 1/30
Mark 4565 6 7 5 2/1

so I need a subtotal row for over/under, rd hrs and paid hrs after each change of EMP#.

The solution u gave me is just overall totals.

 

[vbkris]

then u have to have 4 total variables, have one for each row:
Tot1=0
Tot2=0
Tot3=0

in the Do loop:
Tot1=Tot1+Field1
Tot2=Tot2+Field2
Tot3=Tot3+Field3


Known is handfull, Unknown is worldfull

 

[PeasNCarrots]

Your solution gives me Totals, not Sub-totals for each employee. I will try and figure it out. Thanks for your time.

 

[ChrisHirst]

why not just use the GROUP BY clause and SUM() and let SQL handle it?



Chris.

Indifference will be the downfall of mankind, but who cares?

http://www.candsdesign.co.uk

A website that proves the cobblers kids adage.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.

 

[PeasNCarrots]

This is how my data needs to be...


Emp Name Emp# Over/Under Rd Hrs PaidHrs Date
Chris 1234 3 5 8 1/30
chris 1234 4 6 8 2/1
Week Total 7 11 16
Mark 4565 4 4 4 1/30
Mark 4565 6 7 5 2/1
Week Total 10 11 9

 

[TwoOdd]

This is a little more code, but should do what you want:

Dim varOverUnder
Dim varRdHrs
Dim varPdHrs
Dim CurrEmp

'Set starting values
varOverUnder=0
varRdHrs=0
varPdHrs=0
CurrEmp = RS.Fields("EmpNumber")

Dim I
    For I = 0 To RS.Fields.Count -1 
       response.write "<th BGCOLOR = ""C0C0C0""><font size = ""2""><b>" & RS.Fields(I).Name & "</b></font></th>"
    Next 
%>
</tr>
<%
		'Set Starting Value for 
        Do While Not RS.EOF
			If RS.Fields("EmpNumber")=CurrEmp Then
				%>
				<tr>
				<td align="center" bgcolor="#F7EFDE"><font size = "2">
				<%=RS.Fields("EmpName")%>
				</font></td>
				<td align="center" bgcolor="#F7EFDE"><font size = "2">
				<%=RS.Fields("EmpNumber")%>
				</font></td>
				<td align="center" bgcolor="#F7EFDE"><font size = "2">
				<%
				Response.Write RS.Fields("OverUnder")
				varOverUnder = varOverUnder+RS.Fiels("OverUnder")
				%>
				</font></td>
				<td align="center" bgcolor="#F7EFDE"><font size = "2">
				<%
				Response.Write RS.Fields("RdHrs")
				varRdHrs = varRdHrs+RS.Fiels("RdHrs")
				%>
				</font></td>
				<td align="center" bgcolor="#F7EFDE"><font size = "2">
				<%
				Response.Write RS.Fields("PdHrs")
				varPdHrs = varPdHrs+RS.Fiels("PdHrs")
				%>
				</font></td>
				<td align="center" bgcolor="#F7EFDE"><font size = "2">
				<%=RS.Fields("MyDate")%>
				</font></td>
				</tr>
            <%
            RS.MoveNext
            Else
            'Change of Employee
            'Print Sub Total
            'Do Not move to the next record
				%>
				<tr>
				<td align="center" bgcolor="#F7EFDE" Colspan=2><font size = "2">
				Week Total
				</font></td>
				<td align="center" bgcolor="#F7EFDE"><font size = "2">
				<%=varOverUnder%>
				</font></td>
				<td align="center" bgcolor="#F7EFDE"><font size = "2">
				<%=varRdHrs%>
				</font></td>
				<td align="center" bgcolor="#F7EFDE"><font size = "2">
				<%=varPdHrs%>
				</font></td>
				<td align="center" bgcolor="#F7EFDE"><font size = "2">
				&nbsp;
				</font></td>
				</tr>
				<%
				'reset variables
				varOverUnder=0
				varRdHrs=0
				varPdHrs=0
				CurrEmp=RS.Fields("EmpNumber")
			End If
        Loop
        'We still need the last subtotal because we reached RS.EOF and the subtotal didn't print
        %>
		<tr>
		<td align="center" bgcolor="#F7EFDE" Colspan=2><font size = "2">
		Week Total
		</font></td>
		<td align="center" bgcolor="#F7EFDE"><font size = "2">
		<%=varOverUnder%>
		</font></td>
		<td align="center" bgcolor="#F7EFDE"><font size = "2">
		<%=varRdHrs%>
		</font></td>
		<td align="center" bgcolor="#F7EFDE"><font size = "2">
		<%=varPdHrs%>
		</font></td>
		<td align="center" bgcolor="#F7EFDE"><font size = "2">
		&nbsp;
		</font></td>
		</tr>
</table>

Hope this helps,

TwoOdd
--------------
Good judgment comes from experience, and experience comes from bad judgment.
-- Barry LePatner

 

[TwoOdd]

Sorry, this got left off the top of the code when I pasted it:

<table border="0" width="90%" cellspacing="0" cellpadding="2">
<tr>

TwoOdd
--------------
Good judgment comes from experience, and experience comes from bad judgment.
-- Barry LePatner

 

[TwoOdd]

Now that I posted it, I'm finding typos left and right.

When referencing the value in the recordset it should be:

RS("fieldname")
not
RS.fields("fieldname")

Also, ignore the comment in this code, iI forgot to delete it:

        'Set Starting Value for <--Delete this line
        Do While Not RS.EOF

Hopefully you get the idea of the code (even with a ton of typos),

TwoOdd
--------------
Good judgment comes from experience, and experience comes from bad judgment.
-- Barry LePatner

 

[Tarwn]

Good call TwoOdd, thats the method I was going to post until I scrolled down far enough to see you beat me to it

-T

_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
The never-completed website:

http://www.tiernok.com/

 

[PeasNCarrots]

I already figured it out. Here is my solution which is along the lines of TwoOdd

Dim SQLSum, TotPaid
TotPaid = 0
Do While Not RS.EOF
Response.Write "<tr>"
For i = 0 to RS.Fields.Count -1
If i = 0 then
Response.write "<td align=""left"" bgcolor=""#F7EFDE""><font size = ""2"">" & RS.Fields(I).Value & "</font></td>"
Else
Response.write "<td align=""center"" bgcolor=""#F7EFDE""><font size = ""2"">" & RS.Fields(I).Value & "</font></td>"
End if
Next
Response.Write "</tr>"
empID1 = RS.Fields(1).Value
'Get sum of Paid Hrs Field
TotPaid = Csng(RS.Fields(7).Value) + TotPaid
RS.MoveNext
empID2 = RS.Fields(1).Value
'Show sum of all employees but last one
If empID1 <> empID2 Then
Response.Write "<td colspan=""5""></td><td align=""center"" bgcolor=""#FFBC79""><b>Totals</b></td>"
Response.Write "<td align=""center"" bgcolor=""#F7EFDE"">" & TotPaid & "</td>"
TotPaid = 0
End if
Loop
'Show sum of last employee
Response.Write "<td align=""center"" bgcolor=""#F7EFDE"">" & TotPaid & "</td>"

%>