Field Totals/Sums

	Home Register Search Help Login

Sponsors
Frontpage website templates
Creative Website Templates for FrontPage, Dreamweaver, Flash, SwishMax
Shopping Cart Software
Ecommerce software integrated into Frontpage, Dreamweaver and Golive templates. No monthly fees and available in ASP and PHP versions.
Website Templates
We also have a wide selection of Dreamweaver, Expression Web and Frontpage templates as well as webmaster tools and CSS layouts.

Recent Posts
Todays Posts
Most Active posts
Posts since last visit
My Recent Posts
Mark posts read

Field Totals/Sums

View related threads: (in this forum | in all forums)

Logged in as: Guest

Users viewing this topic: none

Printable Version

All Forums >> Web Development >> ASP, PHP, and Database >> Field Totals/Sums

Page: [1]

Jackie

Posts: 21
From: None
Status: offline

Field Totals/Sums - 5/29/2001 14:59:00

Hi! Me again. Well now I am down to the last details of my database being functional but am stuck on how to get it to Sum each field.

I tried constructing the query in Access and then pasting the sql into my select statement but it didn't work. I've been searching the net all day and still have not figured this out. What I have seen didn't appear to apply to an array so I'm just total confused now!

Here's my code if you can help. Oh, I need totals on the fields 4-12. THANK YOU!

Jackie

P.S. Spooky... if you help me figure this one out I think I will have to cut you a check from my proceeds.

Set rstGetRows = cnnGetRows.Execute("SELECT tblSales.SalesDate, tblSales.DealershipID, tblSales.DeptID, tblSales.Units, tblSales.Sales, tblSales.Gross, tblSales.[Gross]/[Units] AS PNVS, tblSales.Expenses, tblSales.FandIIncome, tblSales.Income, tblSales.Goals, tblSales.Chargebacks FROM tblSales ORDER BY tblSales.DealershipID; ")

arrDBData = rstGetRows.GetRows()

rstGetRows.Close
Set rstGetRows = Nothing
cnnGetRows.Close
Set cnnGetRows = Nothing

iRecFirst = LBound(arrDBData, 2)
iRecLast = UBound(arrDBData, 2)
iFieldFirst = LBound(arrDBData, 1)
iFieldLast = UBound(arrDBData, 1)
%>

<table border="0" width="100%">
<tr>
<%
For I = iRecFirst To iRecLast
Response.Write "<tr>" & vbCrLf

Response.Write "</tr>" & vbCrLf

Next ' I
%>
</tr>
</table>
</body>
</html>

[This message has been edited by Jackie (edited 05-29-2001).]

Spooky

Posts: 26723
Joined: 11/11/1998
From: Middle Earth
Status: offline

RE: Field Totals/Sums - 5/29/2001 20:15:00

I havent done a lot of currency addition, but I assume something like this would work :

If Trim(arrDBData (J, I)) <> " " then
Response.Write vbTab & "<td valign=center></td><td>" & FormatCurrency(arrDBData (J, I),2) & "</td>" & vbCrLf

TotalSum = TotalSum + arrDBData (J, I)

End if

Then write TotalSum at the end of the page (formated)

(in reply to Jackie)

Jackie

Posts: 21
From: None
Status: offline

RE: Field Totals/Sums - 5/30/2001 14:00:00

Oh... so close yet so far away!

Using this code:

<table border="0" width="100%">
<tr>
<%
For I = iRecFirst To iRecLast
Response.Write "<tr>" & vbCrLf

Next ' I
%>
</tr>
<tr>
<td valign="center">Total:</td>
<td valign="center"><%=FormatCurrency(RowTotal,2)%> </td>
</tr>
</table>
</body>
</html>

I recieved a total but it was a "grand total" of all columns. I need it to total "each" column.

Any thoughts on how to accomplish this?

Thanks again!

Jackie

(in reply to Jackie)

Spooky

Posts: 26723
Joined: 11/11/1998
From: Middle Earth
Status: offline

RE: Field Totals/Sums - 5/30/2001 14:33:00

Youd expand it to do each field :

If J = 4 then
TotalSum4 = TotalSum4 + arrDBData (J, I)
Elseif J = 5
TotalSum5 = TotalSum5 + arrDBData (J, I)
Elseif.......
etc
End if

------------------
§þððk¥
"I am Spooky of Borg. Prepare to be assimilated, babycakes!"
Subscribe to OutFront News
Database / DRW Q & A
The Spooky Login!

(in reply to Jackie)

Jackie

Posts: 21
From: None
Status: offline

RE: Field Totals/Sums - 5/31/2001 20:03:00

Oh how I wish I would have not started this project!

Problem 1:
Returns a row, ok... good, but the values are all $0.00.

Problem 2: TotalSum12 gives me an error
"subscript out of range: '12'

Here's the code, any idea what I am doing wrong?

Thanks again! Jackie

<%
For I = iRecFirst To iRecLast
Response.Write "<tr>" & vbCrLf

For J = iField To iFieldLast
Select Case J
Case 4
If Trim(arrDBData (J, I)) <> " " then
Response.Write vbTab & "<td valign=center></td><td>" & FormatNumber(arrDBData (J, I),2) & "</td>" & vbCrLf
End if
Case 5,6,7,8,9,10,11,12
If Trim(arrDBData (J, I)) <> " " then
Response.Write vbTab & "<td valign=center></td><td>" & FormatCurrency(arrDBData (J, I),2) & "</td>" & vbCrLf
End if
Case else
If Trim(arrDBData (J, I)) <> " " then
Response.Write vbTab & "<td valign=center></td><td>" & arrDBData (J, I) & "</td>" & vbCrLf
End if
End select
Next ' J
Response.Write "</tr>" & vbCrLf
If J = "4" then
TotalSum4 = TotalSum4 + arrDBData (J, I)
Elseif J = "5" then
TotalSum5 = TotalSum5 + arrDBData (J, I)
Elseif J = 6 then
TotalSum6 = TotalSum6 + arrDBData (J, I)
Elseif J = 7 then
TotalSum7 = TotalSum7 + arrDBData (J, I)
Elseif J = 8 then
TotalSum8 = TotalSum8 + arrDBData (J, I)
Elseif J = 9 then
TotalSum9 = TotalSum9 + arrDBData (J, I)
Elseif J = 10 then
TotalSum10 = TotalSum10 + arrDBData (J, I)
Elseif J = 11 then
TotalSum11 = TotalSum11 + arrDBData (J, I)
Elseif J = 12 then
TotalSum12 = TotalSum12 + arrDBData (J, I)
End if

Next ' I

%>
</tr>
<tr>
<td valign="center">Total:</td>
<td valign="center"><%=FormatNumber(TotalSum4,2)%> </td>
<td valign="center"><%=FormatCurrency(TotalSum5,2)%> </td>
<td valign="center"><%=FormatCurrency(TotalSum6,2)%> </td>
<td valign="center"><%=FormatCurrency(TotalSum7,2)%> </td>
<td valign="center"><%=FormatCurrency(TotalSum8,2)%> </td>
<td valign="center"><%=FormatCurrency(TotalSum9,2)%> </td>
<td valign="center"><%=FormatCurrency(TotalSum10,2)%> </td>
<td valign="center"><%=FormatCurrency(TotalSum11,2)%> </td>
<td valign="center"><%=FormatCurrency(TotalSum12,2)%> </td>
</tr>
</table>
</body>
</html>

[This message has been edited by Jackie (edited 05-31-2001).]

(in reply to Jackie)

Spooky

Posts: 26723
Joined: 11/11/1998
From: Middle Earth
Status: offline

RE: Field Totals/Sums - 5/31/2001 16:56:00

All of the code :

If J = "4" then
.......
End if

Should be within the "Case 5,6,7,8,9,10,11,12

(in reply to Jackie)

Jackie

Posts: 21
From: None
Status: offline

RE: Field Totals/Sums - 6/1/2001 15:14:00

ok... I placed all the code where you said:

Case 5,6,7,8,9,10,11,12
If Trim(arrDBData (J, I)) <> " " then
Response.Write vbTab & "<td valign=center></td><td>" & FormatCurrency(arrDBData (J, I),2) & "</td>" & vbCrLf
End If
If J = "5" then
TotalSum5 = TotalSum5 + arrDBData (J, I)
Elseif J = "6" then
TotalSum6 = TotalSum6 + arrDBData (J, I)
Elseif J = "7" then
TotalSum7 = TotalSum7 + arrDBData (J, I)
Elseif J = "8" then
TotalSum8 = TotalSum8 + arrDBData (J, I)
Elseif J = "9" then
TotalSum9 = TotalSum9 + arrDBData (J, I)
Elseif J = "10" then
TotalSum10 = TotalSum10 + arrDBData (J, I)
Elseif J = "11" then
TotalSum11 = TotalSum11 + arrDBData (J, I)
Elseif J = "12" then
TotalSum12 = TotalSum12 + arrDBData (J, I)
End if
Case else

And then if I leave the code:

Then I get the following error:

Microsoft VBScript runtime error '800a000d'

Type mismatch: 'FormatCurrency'

/database/reportb.asp, line 111

If I take out the <%=FormatCurrency(TotalSum5,2)%> and replace it with <%=TotalSum5%> then I recieve the word "Total:" and nothing else.

Is the first part hitting a null and erroring out, like before I put the If Trim...?

::::Tearing hair out and ready to jump off the Microsoft building:::::

Jackie

[This message has been edited by Jackie (edited 06-01-2001).]

(in reply to Jackie)


Spooky Posts: 26723 Joined: 11/11/1998 From: Middle Earth Status: offline	RE: Field Totals/Sums - 6/1/2001 17:22:00 Yes, it needs to be inputing a valid currency figure. How are you inputing the values? Checking its null or empty is usually sufficient. There arent non numerals in there? (in reply to Jackie)

Jackie

Posts: 21
From: None
Status: offline

RE: Field Totals/Sums - 6/1/2001 17:31:00

Hi. The values are there. Currently each column has a least 1 value but this may not always be the case. All of the values in case 5-12 are currency. Am I answering your question correctly?

We have it checking for null in the first part but, if this is the problem, how do I do that for the TotalSum row?

Thanks again for your help and patience Spooky!

Jackie

(in reply to Jackie)


Spooky Posts: 26723 Joined: 11/11/1998 From: Middle Earth Status: offline	RE: Field Totals/Sums - 6/2/2001 23:39:00 Where did we get to with this one? Im not sure what we have done so far (in reply to Jackie)

Jackie

Posts: 21
From: None
Status: offline

RE: Field Totals/Sums - 6/3/2001 16:52:00

Let's see. At first I couldn't get it to format the columns to number or currency because some fields were null. We resolved that with "If trim."

Now I need it to total each column and I think I am running into the null problem (some fields are null but each column has at least one value in it) again and not sure how to account for that. I was thinking maybe: If Trim... or.... then. But not sure if that is the solution and if it is how to write the statement. And perhaps this isn't the problem at all.

Currently I am not getting any errors but then I don't get any totals either... just a blank row. If I change and use this code:

then I get a type mismatch error. That's what makes me think it is having problems with the nulls in the fields.

I pasted all my current code in a couple of posts back. Hope I have clarified this a little better... if not, let me know.

Thanks!

Jackie

(in reply to Jackie)

Jackie

Posts: 21
From: None
Status: offline

RE: Field Totals/Sums - 6/7/2001 20:59:00

Spooky,

You're back(?). Help please.

Jackie

(in reply to Jackie)

Spooky

Posts: 26723
Joined: 11/11/1998
From: Middle Earth
Status: offline

RE: Field Totals/Sums - 6/7/2001 19:25:00

If you just write <%=TotalSum5%>

What result do you get?
Have all the results from "5" been null up to when you totalled?

------------------
§þððk¥
"I am Spooky of Borg. Prepare to be assimilated, babycakes!"
Subscribe to OutFront News
Database / DRW Q & A
The Spooky Login!

(in reply to Jackie)

Jackie

Posts: 21
From: None
Status: offline

RE: Field Totals/Sums - 6/7/2001 22:20:00

I tried your code and I recieved nothing (except the word Total

on the lines that should be displaying totals.

<td valign="center">Total:</td>
<td valign="center"><%=TotalSum5%> </td>
... and so on

There are 13 rows of test data. Within those 13 rows the minimum of values in any column is 4. On TotalSum5 there are null values in rows 3 and 4.

Thanks again!

Jackie

(in reply to Jackie)

Spooky

Posts: 26723
Joined: 11/11/1998
From: Middle Earth
Status: offline

RE: Field Totals/Sums - 6/8/2001 15:11:00

I meant to write TotalSum5 at the bottom of the page once all the columns had been added to it.

Is that what you did?

eg, you loop through data "5" with this code:

TotalSum5 = TotalSum5 + arrDBData (J, I)

If only 2 cells are null, the remainder should be added to the variable TotalSum5

(in reply to Jackie)

Jackie

Posts: 21
From: None
Status: offline

RE: Field Totals/Sums - 6/11/2001 20:19:00

.....
I meant to write TotalSum5 at the bottom of the page once all the columns had been added to it.
Is that what you did?

eg, you loop through data "5" with this code:

TotalSum5 = TotalSum5 + arrDBData (J, I)

.....

Yes, that is what I did. The code in my previous post was placed after all the columns were added. Here is my code again so you can inspect it.

Thanks! Jackie

.......

<%
For I = iRecFirst To iRecLast
Response.Write "<tr>" & vbCrLf

For J = iField To iFieldLast
Select Case J
Case 4
If Trim(arrDBData (J, I)) <> " " then
Response.Write vbTab & "<td valign=center></td><td>" & FormatNumber(arrDBData (J, I),2) & "</td>" & vbCrLf
End if
Case 5,6,7,8,9,10,11,12
If Trim(arrDBData (J, I)) <> " " then
Response.Write vbTab & "<td valign=center></td><td>" & FormatCurrency(arrDBData (J, I),2) & "</td>" & vbCrLf
End If
If J = "5" then
TotalSum5 = TotalSum5 + arrDBData (J, I)
Elseif J = "6" then
TotalSum6 = TotalSum6 + arrDBData (J, I)
Elseif J = "7" then
TotalSum7 = TotalSum7 + arrDBData (J, I)
Elseif J = "8" then
TotalSum8 = TotalSum8 + arrDBData (J, I)
Elseif J = "9" then
TotalSum9 = TotalSum9 + arrDBData (J, I)
Elseif J = "10" then
TotalSum10 = TotalSum10 + arrDBData (J, I)
Elseif J = "11" then
TotalSum11 = TotalSum11 + arrDBData (J, I)
Elseif J = "12" then
TotalSum12 = TotalSum12 + arrDBData (J, I)
End if
Case else
If Trim(arrDBData (J, I)) <> " " then
Response.Write vbTab & "<td valign=center></td><td>" & arrDBData (J, I) & "</td>" & vbCrLf
End if
End select
Next ' J
Response.Write "</tr>" & vbCrLf

Next ' I

%>
</tr>
<tr>
<td valign="center">Total:</td>
<td valign="center"><%=TotalSum5%> </td>
<td valign="center"><%=TotalSum6%> </td>
<td valign="center"><%=TotalSum7%> </td>
<td valign="center"><%=TotalSum8%> </td>
<td valign="center"><%=TotalSum9%> </td>
<td valign="center"><%=TotalSum10%> </td>
<td valign="center"><%=TotalSum11%> </td>
<td valign="center"><%=TotalSum12%> </td>
</tr>
</table>
</body>
</html>

(in reply to Jackie)

Spooky

Posts: 26723
Joined: 11/11/1998
From: Middle Earth
Status: offline

RE: Field Totals/Sums - 6/11/2001 18:42:00

Its a bit hard to tell without seeing (or having) working code. I like live examples

Does it perform as expected without the summing? (totalsum5)

Do you have a url I can see?

"TotalSum5 there are null values in rows 3 and 4"

So every other column has a numeric value - without totalling?

(in reply to Jackie)


Spooky Posts: 26723 Joined: 11/11/1998 From: Middle Earth Status: offline	RE: Field Totals/Sums - 6/12/2001 18:00:00 Ive received your note, Im away from my PC at the moment. I see some lines contain $ and () ? where do they fit into the equation> ------------------ §þððk¥ "I am Spooky of Borg. Prepare to be assimilated, babycakes!" Subscribe to OutFront News Database / DRW Q & A The Spooky Login! (in reply to Jackie)

Jackie

Posts: 21
From: None
Status: offline

RE: Field Totals/Sums - 6/14/2001 14:34:00

Thought I would post Spooky's fix to my problem. All is working now!

Thanks Spooky!

Jackie
**********
Looking at the code again, the calculation is outside of the initial null check :

If Trim(arrDBData (J, I)) <> " " AND isNumeric(arrDBData (J, I)) then
Response.Write vbTab & "<td valign=center></td><td>" & FormatCurrency(arrDBData (J, I),2) & "</td>" & vbCrLf
====> End If <====== Move this down to the bottom.
If J = "5" then
TotalSum5 = TotalSum5 + arrDBData (J, I)
Elseif J = "6" then
TotalSum6 = TotalSum6 + arrDBData (J, I)
Elseif J = "7" then
TotalSum7 = TotalSum7 + arrDBData (J, I)
Elseif J = "8" then
TotalSum8 = TotalSum8 + arrDBData (J, I)
Elseif J = "9" then
TotalSum9 = TotalSum9 + arrDBData (J, I)
Elseif J = "10" then
TotalSum10 = TotalSum10 + arrDBData (J, I)
Elseif J = "11" then
TotalSum11 = TotalSum11 + arrDBData (J, I)
Elseif J = "12" then
TotalSum12 = TotalSum12 + arrDBData (J, I)
End if
End if <===== to here.

I think that may help for a start?