Help with totals please 1

timdodgson · Dec 11, 2002

I am very new to asp and also quite new to access so please alittle help. My problem i havw access database that i have made an asp from by using dreamweaver you could vist this page at

http://www.businessintegration.biz/greenbank_invoice.asp

the page works by pulling simple data from an access database "hourly rate" "Hours Visited" "Date" "Spares" and "Spares cost" come fom a query "Total"is made by hourly rate * Hours visited + spares cost this all works ok but what i want is a grand total at the bottom ie under totals colume
any help please and thanks in advance
Tim Dodgson
Tim Dodgson
timdodgson@yahoo.co.uk

http://www.ukitsolutions.net

adam0101 · Dec 11, 2002

As you are looping through the recordset, you could add the amount to a variable and display the variable at the end.

Do While Not rs.EOF
...print your table row...
total1=total1+rs.Fields("column1&quot

total2=total2+rs.Fields("column2&quot

total3=total3+rs.Fields("column3&quot

rs.MoveNext
Loop
...Print out total variables here... Adam

http://www.adventex.com

timdodgson · Dec 12, 2002

Sounds like a good reply Thank You bit i am not sure how to insert it into my page the column i am trying to total is called totals any more help PLEEEEEASa
many Thanks in Advance
Tim Dodgson
Tim Dodgson
timdodgson@yahoo.co.uk

http://www.ukitsolutions.net

mwa · Dec 12, 2002

OK... if you are only wanting the total of the totals column, inside of your loop, you will need to set a variable (grandtotal) equal to your total formula + the old grandtotal:

total = hourly rate * Hours visited + spares cost
grandtotal = grandtotal + total

after you get done looping through the records, you can the write out the grandtotal variable to the screen:

response.write "Grand Total: " & grandtotal

If you still don't understand, post your code and I will try to walk you through it using your code...

mwa

timdodgson · Dec 12, 2002

Thank You so much for your help but i just have not got the knolage to hang code this so as you sujested iwill post my code

CODE
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset&quot

Recordset1.ActiveConnection = MM_Customer_STRING
Recordset1.Source = "SELECT * FROM [Greenbank School]"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = 10
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>
<%
' *** Recordset Stats, Move To Record, and Go To Record: declare stats variables

Dim Recordset1_total
Dim Recordset1_first
Dim Recordset1_last

' set the record count
Recordset1_total = Recordset1.RecordCount

' set the number of rows displayed on this page
If (Recordset1_numRows < 0) Then
Recordset1_numRows = Recordset1_total
Elseif (Recordset1_numRows = 0) Then
Recordset1_numRows = 1
End If

' set the first and last displayed record
Recordset1_first = 1
Recordset1_last = Recordset1_first + Recordset1_numRows - 1

' if we have the correct record count, check the other stats
If (Recordset1_total <> -1) Then
If (Recordset1_first > Recordset1_total) Then
Recordset1_first = Recordset1_total
End If
If (Recordset1_last > Recordset1_total) Then
Recordset1_last = Recordset1_total
End If
If (Recordset1_numRows > Recordset1_total) Then
Recordset1_numRows = Recordset1_total
End If
End If
%>
<%
' *** Recordset Stats: if we don't know the record count, manually count them

If (Recordset1_total = -1) Then

' count the total records by iterating through the recordset
Recordset1_total=0
While (Not Recordset1.EOF)
Recordset1_total = Recordset1_total + 1
Recordset1.MoveNext
Wend

' reset the cursor to the beginning
If (Recordset1.CursorType > 0) Then
Recordset1.MoveFirst
Else
Recordset1.Requery
End If

' set the number of rows displayed on this page
If (Recordset1_numRows < 0 Or Recordset1_numRows > Recordset1_total) Then
Recordset1_numRows = Recordset1_total
End If

' set the first and last displayed record
Recordset1_first = 1
Recordset1_last = Recordset1_first + Recordset1_numRows - 1

If (Recordset1_first > Recordset1_total) Then
Recordset1_first = Recordset1_total
End If
If (Recordset1_last > Recordset1_total) Then
Recordset1_last = Recordset1_total
End If

End If
%>
<html>
<head>
<title>Greenbank Invoice</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script language="JavaScript" type="text/JavaScript">
<!--
function MM_reloadPage(init) { //reloads the window if Nav4 resized
if (init==true) with (navigator) {if ((appName=="Netscape&quot

&&(parseInt(appVersion)==4)) {
document.MM_pgW=innerWidth; document.MM_pgH=innerHeight; onresize=MM_reloadPage; }}
else if (innerWidth!=document.MM_pgW || innerHeight!=document.MM_pgH) location.reload();
}
MM_reloadPage(true);
//-->
</script>
</head>

<body>
<div id="Layer1" style="position:absolute; left:0px; top:0px; width:550px; height:420px; z-index:1"></div>
<div id="Layer2" style="position:absolute; left:448px; top:6px; width:72px; height:96px; z-index:2"><img src="CAIVUZSL.jpg" width="102" height="104"></div>
<div id="Layer3" style="position:absolute; left:10px; top:6px; width:345px; height:40px; z-index:3"><font color="#006699" size="6" face="Arial, Helvetica, sans-serif">Business
Integr@tion </font></div>
<div id="Layer4" style="position:absolute; left:10px; top:41px; width:347px; height:118px; z-index:4">
<p><font color="#000000" size="2" face="Arial, Helvetica, sans-serif">163 Old
Chapel St,<br>
Edgeley,</font><font size="2"><br>
<font face="Arial, Helvetica, sans-serif">Stockport,<br>
Cheshire,<br>
SK3 9JL<br>
<strong>T</strong>el: 0161 474 7968 <strong>F</strong>ax: 0161 474 0206<br>
<strong>E</strong>mail <a href="mailto:greenbankinvoice@businessintegration.biz">greenbankinvoice@businessintegration.biz</a></font></font>
<br>
</p>
</div>
<div id="Layer5" style="position:absolute; left:10px; top:169px; width:407px; height:23px; z-index:5"><font color="#006699" size="6" face="Arial, Helvetica, sans-serif">Invoice
to Greenbank School</font></div>
<div id="Layer6" style="position:absolute; left:10px; top:203px; width:539px; height:6px; z-index:6">
<hr>
</div>

<div id="Layer7" style="position:absolute; left:10px; top:229px; width:540px; height:70px; z-index:7">
<table width="540" border="0" bordercolor="#FFFFFF">
<tr>
<td width="90"><div align="center"><font color="#006699" size="2" face="Arial, Helvetica, sans-serif">Hourly
Rate</font></div></td>
<td width="90"><div align="center"><font color="#006699" size="2" face="Arial, Helvetica, sans-serif">Hours
Visited</font></div></td>
<td width="90"><div align="center"><font color="#006699" size="2" face="Arial, Helvetica, sans-serif">Date</font></div></td>
<td width="90"><div align="center"><font color="#006699" size="2" face="Arial, Helvetica, sans-serif">Spares</font></div></td>
<td width="90"><div align="center"><font color="#006699" size="2" face="Arial, Helvetica, sans-serif">Spares
Cost</font></div></td>
<td width="90"><div align="center"><font color="#006699" size="2" face="Arial, Helvetica, sans-serif">Total</font></div></td>
</tr>
<% While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF)) %>
<tr>
<td><div align="center">£<%=(Recordset1.Fields.Item("Hourly Rate&quot

.Value)%></div></td>
<td><div align="center"><%=(Recordset1.Fields.Item("Hours Visted&quot

.Value)%></div></td>
<td><div align="center"><font size="2"><%=(Recordset1.Fields.Item("Date&quot

.Value)%></font></div></td>
<td><div align="center"><font size="2"><%=(Recordset1.Fields.Item("Spares&quot

.Value)%></font></div></td>
<td><div align="center">£<%=(Recordset1.Fields.Item("Spares Cost&quot

.Value)%></div></td>
<td><div align="center">£<font size="3"><%=(Recordset1.Fields.Item("Totals&quot

.Value)%></font></div></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
Recordset1.MoveNext()
Wend
%>
</table>
THIS IS WHERE I WOULD LIKE THE GRAND TOTAL
<br>
<hr>
</div>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>
CODE
Again Many Thanks
Tim Dodgson
Tim Dodgson
timdodgson@yahoo.co.uk

http://www.ukitsolutions.net

mwa · Dec 13, 2002

[red]try replacing this:[/red]

Code:

<% While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF)) %>
    <tr> 
      <td><div align=&quot;center&quot;>£<%=(Recordset1.Fields.Item(&quot;Hourly Rate&quot;).Value)%></div></td>
      <td><div align=&quot;center&quot;><%=(Recordset1.Fields.Item(&quot;Hours Visted&quot;).Value)%></div></td>
      <td><div align=&quot;center&quot;><font size=&quot;2&quot;><%=(Recordset1.Fields.Item(&quot;Date&quot;).Value)%></font></div></td>
      <td><div align=&quot;center&quot;><font size=&quot;2&quot;><%=(Recordset1.Fields.Item(&quot;Spares&quot;).Value)%></font></div></td>
      <td><div align=&quot;center&quot;>£<%=(Recordset1.Fields.Item(&quot;Spares Cost&quot;).Value)%></div></td>
      <td><div align=&quot;center&quot;>£<font size=&quot;3&quot;><%=(Recordset1.Fields.Item(&quot;Totals&quot;).Value)%></font></div></td>
    </tr>
    <% 
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  Recordset1.MoveNext()
Wend
%>
  </table>
  THIS IS WHERE I WOULD LIKE THE GRAND TOTAL

[red]with this:[/red]

Code:

<% DIM curGrandTotal
    curGrandTotal = 0
    While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF)) %>
    <tr> 
      <td><div align=&quot;center&quot;>£<%=(Recordset1.Fields.Item(&quot;Hourly Rate&quot;).Value)%></div></td>
      <td><div align=&quot;center&quot;><%=(Recordset1.Fields.Item(&quot;Hours Visted&quot;).Value)%></div></td>
      <td><div align=&quot;center&quot;><font size=&quot;2&quot;><%=(Recordset1.Fields.Item(&quot;Date&quot;).Value)%></font></div></td>
      <td><div align=&quot;center&quot;><font size=&quot;2&quot;><%=(Recordset1.Fields.Item(&quot;Spares&quot;).Value)%></font></div></td>
      <td><div align=&quot;center&quot;>£<%=(Recordset1.Fields.Item(&quot;Spares Cost&quot;).Value)%></div></td>
      <td><div align=&quot;center&quot;>£<font size=&quot;3&quot;><%=(Recordset1.Fields.Item(&quot;Totals&quot;).Value)%></font></div></td>
    </tr>
    <% 
  curGrandTotal = curGrandTotal + Recordset1.Fields.Item(&quot;Totals&quot;).Value
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  Recordset1.MoveNext()
Wend
%>
  </table>
  <%'THIS IS WHERE I WOULD LIKE THE GRAND TOTAL%>
  <B>Grand Total: <%=formatcurrency(curGrandTotal)%><B>

[red]Try that and let me know how it goes...

mwa[/red]

timdodgson · Dec 13, 2002

Thank You so much
It worked OK i have been on a very steep learning curve (everest) i had actuly nearly done it brackets and dots doing my head in

Again thank youvery much
If i can help you i have mcse nvq4 sage certified
just give us a shoot
timdodgson@yahoo.co.uk

Timdodgson
Tim Dodgson
timdodgson@yahoo.co.uk

http://www.ukitsolutions.net

timdodgson · Dec 13, 2002

sorry meant to say the formatcurencey command came up as dollers even though i have pounds set in control panel i just changed code to £ sign just wondered if you have any ideas
Tim Dodgson
timdodgson@yahoo.co.uk

http://www.ukitsolutions.net

mwa · Dec 13, 2002

Sorry about that... just habit on the formatcurrency... I'm not sure how to put £ in there... I'm glad the other worked for you...

I'll keep you in mind if I ever cross a path where I need something...

mwa

P.S....

Not that I'm asking for this, but for future reference... If someone helps you out, you can show everyone else by giving that person a star... Just click the link that says "Mark this post as a helpful/expert post!"... Later...

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Help with totals please 1

timdodgson

Technical User

adam0101

Programmer

timdodgson

Technical User

mwa

Programmer

timdodgson

Technical User

mwa

Programmer

timdodgson

Technical User

timdodgson

Technical User

mwa

Programmer

Similar threads

Part and Inventory Search

Sponsor