Adding values from database query

[stevem2004]

Hi, I have the following data from a access db

ID Date User System Time Taken
4 14/12/04 esol Internet 5
6 15/12/04 EsOL Internet 2250
8 15/12/04 dkfha Testing 50
9 15/12/04 Villy Internet 1350
10 16/12/04 Webcom Internet 900
11 20/12/04 Me Misc 15
12 20/12/04 Task Duty Jobs 180
13 20/12/04 Gavin Misc 210

which is retrieved by the following code:

<table width="700" cellpadding="2" cellspacing="0" border="1" bordercolor="#809CC9">
<tr>
<th class="normal">ID</th>
<th class="normal">Date</th>
<th class="normal">User</th>
<th class="normal">Created by</th>
<th class="normal">System</th>
<th class="normal">Query</th>
<th class="normal">Time Taken</th>
</tr>
<%
dim conn, rs, strconn, strSQL, strDate

strconn = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("../database/database.mdb")
set conn = server.createobject("adodb.connection")
conn.open strconn

strSQL = "SELECT * FROM table WHERE status = 'Closed';"
set rs = server.createobject("adodb.recordset")
rs.open strSQL, conn, 3, 3

Dim i, count
Dim arrIDs()
Dim dte

count = 0
rs.MoveFirst

For i = 0 To rs.RecordCount
If Not rs.EOF Then
dte = DateValue(rs("date"))

Dim intDiff
intDiff = DateDiff("d", dte, DateAdd("d", -1, Date()))
If intDiff >= 0 And intDiff <= 30 Then
ReDim Preserve arrIDs(count)
arrIDs(count) = rs("id")
count = count + 1
End If

rs.MoveNext
End If
Next
%>
<p class="normal">
<%
For i = 0 To UBound(arrIDs)
' Response.Write (arrIDs(i) & ", ")
Next
%>

<%
strQuery = "SELECT * FROM table WHERE "

For i = 0 To UBound(arrIDs)
If i > 0 Then
strQuery = strQuery & " OR id = " & arrIDs(i)
Else
strQuery = strQuery & "id = " & arrIDs(i)
End If
Next

Set conn = Server.CreateObject("ADODB.Connection")
strConn = "DBQ=" & Server.MapPath("../database/data.mdb") & ";" & _
"Driver={Microsoft Access Driver (*.mdb)}"
conn.Open(strConn)

Set RS = Server.CreateObject("ADODB.RecordSet")
RS.Open(strQuery), conn, 3

If RS.EOF and RS.BOF Then
Response.Write ("No Logs Last week")
Response.End
End If

RS.MoveFirst
For i = 0 To RS.RecordCount
If Not RS.EOF Then
Response.Write ("<tr><td class=""normal""><a href=""../view/view.asp?id=" & rs("id") & """>" & rs("id") & "</a>" & "</td><td class=""normal"">" & rs("date") & "</td><td class=""normal"">" & rs("user") & "</td><td class=""normal"">" & rs("rscmember") & "</td><td class=""normal"">" & rs("system") & "</td><td class=""normal"">" & rs("query") & "</td><td class=""normal"">" & rs("timetaken") & "</td></tr>")

RS.MoveNext
End If
Next
%>
</table>


What I would like to do, but can't think how, is to add the time taken field up (which is in minutes), as you can see this code retrieves the last week's worth of data, does anybody know how I can add these values, within this query?

Many Thanks

Steve

[lmf232s]

you just need to keep a running total of one field correct?

Create a Variable, well say

Dim TotalTime

Then while you are executing your code do this to add accumulate
each field

For i = 0 to rs.RecordCount
TotalTime = TotalTime + objRS("TotalTimeField")
Next

This will be in your loop.
No once the loop is done you can display
TotalTime.
If you want it in min, you could leave it alone or
you could divide by 60 to get hours/min.

[stevem2004]

Yes I want a running total from all users.

Does it matter were I put this loop??, as at the moment it's returning a figure of 55, which can't be correct as there are 1000's in the returned figures.

[lmf232s]

where ever you have the loop that produces this

4 14/12/04 esol Internet 5
6 15/12/04 EsOL Internet 2250
8 15/12/04 dkfha Testing 50
9 15/12/04 Villy Internet 1350
10 16/12/04 Webcom Internet 900
11 20/12/04 Me Misc 15
12 20/12/04 Task Duty Jobs 180
13 20/12/04 Gavin Misc 210

you would put

TotalTime = TotalTime + objRS("FieldNameThatYouWantToAdd")

This way everytime you move to a new record you will add that records
total to your running total.

[stevem2004]

That's what I did, I put the loop in front of

For i = 0 To RS.RecordCount
If Not RS.EOF Then
'| Print out the value of the records here.
Response.Write ("<tr><td class=""normal""><a href=""../view/view.asp?id=" & rs("id") & """>" & rs("id") & "</a>" & "</td><td class=""normal"">" & rs("date") & "</td><td class=""normal"">" & rs("user") & "</td><td class=""normal"">" & rs("rscmember") & "</td><td class=""normal"">" & rs("system") & "</td><td class=""normal"">" & rs("query") & "</td><td class=""normal"">" & rs("timetaken") & "</td></tr>")

RS.MoveNext
End If
Next

Which is where the table is result is printed out.

[lmf232s]

you do not need to create a new loop but just embed the
variable keeping track of the calculate into the other loop



For i = 0 To RS.RecordCount
If Not RS.EOF Then
'| Print out the value of the records here.
Response.Write ("<tr><td class=""normal""><a href=""../view/view.asp?id=" & rs("id") & """>" & rs("id") & "</a>" & "</td><td class=""normal"">" & rs("date") & "</td><td class=""normal"">" & rs("user") & "</td><td class=""normal"">" & rs("rscmember") & "</td><td class=""normal"">" & rs("system") & "</td><td class=""normal"">" & rs("query") & "</td><td class=""normal"">" & rs("timetaken") & "</td></tr>")
TotalTime = TotalTime + rs("timetaken")
RS.MoveNext
End If
Next


now you might get an error about type mismatch ( I THINK)
this will also depend on the data type you have timetake as in the
database. If you have a problem with this you may want to try

TotalTime = cDBL(TotalTime) + rs("timetaken")

[stevem2004]

Sorry it's getting late in the day here, I should have noticed.

Many Thanks, all working now

[lmf232s]

good, glad i could help