Hi, I'm working on a new website for my travel company. The newest feature we want to add is points reward system. I've written the script to pull the records for the specified user from the database and loop through and show all their point earnings. Now the next thing I need is to show a balance of all their total points. I've included pictures below of the database table, and the area that shows up on the My Account section of the website.

I just can't seem to get the SUM function right! I'm always getting an error, so all help is appreciated!

- Microsoft Access Table (for POINTS)
LINK: http://67.171.119.96/images/ads/points_1.png

- Webpage Showing Points
LINK: http://67.171.119.96/images/ads/my_points_ad.png

Thanks!

- Kyle J

I didn't view your links but you want to do something like:

SELECT Sum(points) From Table where userID = 'user'

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

How does the code for your SUM function look like right now?

If you're displaying the data in a loop while getting it from a recordset, you can simply keep a running total. E.g.:

total = total + CInt(myRecordset("Points"))
myRecordset.MoveNext

Alternatively, you can fire a separate query against the database that SUMS the data while grouping it by the user's ID.

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

Yes, I've tried this, but I get this error:

Microsoft VBScript compilation error- Error '800a03fd'

Expected 'Case'

/content/my_points.html, line 16

SELECT Sum(ADD_USER) From POINTS where ADD_USER = '" & USERNAME & "'
-------^

Thanks for any help!

Ok, I tried this code and it's working to a point. The problem is it's not the right total.

The code is:

<%
SET POINT_VIEW = SERVER.CREATEOBJECT("ADODB.RECORDSET")
POINT_VIEW.OPEN "SELECT * FROM POINTS WHERE ADD_USER = '" & USERNAME & "' ORDER BY ADD_ID DESC", DBCONN, 1, 4

balance = balance + Int(POINT_VIEW("ADD_POINTS"))
POINT_VIEW.MoveNext
%>

The values in the table are:

10
5
15
-15
-10

(for this one user)

And the result I'm getting is:

-10 (although it should be 5)


Any answers to this? Thanks in advance!

I prefer to do this totally in SQL.

This is my SQL statement that i used (I have used a temp table to recreate your data)

DECLARE @tbl Table(value int)
Insert into @tbl(value) Values(10)
Insert into @tbl(value) Values(5)
Insert into @tbl(value) Values(15)
Insert into @tbl(value) Values(-15)
Insert into @tbl(value) Values(-10)

SELECT Sum(Value) as Points from @tbl --When executed, the value 5 is returned in my result set

So instead of
POINT_VIEW.OPEN "SELECT * FROM POINTS WHERE ADD_USER = '" & USERNAME & "' ORDER BY ADD_ID DESC", DBCONN, 1, 4

why not try
POINT_VIEW.OPEN "SELECT sum(points) as points FROM POINTS WHERE ADD_USER = '" & USERNAME & "' ORDER BY ADD_ID DESC", DBCONN, 1, 4

hth

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

Thanks dparsons - Before I ruin all of my code that I've done and try the one your provided, I just wanted to clarify some stuff. You have set the values into the script there, but I would like to remind you that these values will be constantly changing, as points are added and deducted from the user's account.

Do you know any reason why the "balance = balance + Int..." wouldn't be working?

Thanks

Okay, for anyone willing to help, this is the webpage (portion of the webpage) that I'm working with. It displays the individual records of point accumulations, but I need it to total correctly. Thanks (and hope this helps)!

<%
SET POINT_VIEW = SERVER.CREATEOBJECT("ADODB.RECORDSET")
POINT_VIEW.OPEN "SELECT * FROM POINTS WHERE ADD_USER = '" & USERNAME & "' ORDER BY ADD_ID DESC", DBCONN, 1, 4

balance = balance + Int(POINT_VIEW("ADD_POINTS"))
POINT_VIEW.MoveNext
%>

<div class="headBar"><div id="addIcos"><img src="/images/icons/money_dollar.png"></div> myPoints Account - <a style="color: #666; text-decoration: underline" href="/mytravel/mypoints.asp">How to Earn Points</a> <div id="helpCons"><a href="#" onmouseover="showHelp(/*id*/'points',1,'style.visibility','visible')" onmouseout="resetHelp()"><img src="/images/icons/help.png" border="0"></a></div></div>
<div class="smllBar">
<%
POINT_VIEW.PageSize = MESSAGEPERPAGE
IF NOT POINT_VIEW.EOF THEN POINT_VIEW.AbsolutePage = currentpage

IF POINT_VIEW.EOF THEN
%>

<div class="noAlrtTop">You have no myPoints available.</div>
<div class="noAlrtTxt">It's easy to earn more points though! Just login to your account regularly or make reservations, and you'll instantly earn points!</div>
<%ELSE%>
    <table cellpadding="0" cellspacing="0" height="80%" width="100%">
        <tr>
            <td class="pointTabA" id="blogHead">Category</td>
            <td class="pointTabB" id="mailHead">Comment</td>
            <td class="pointTabC" id="itinHead">Points</td>
        </tr>
<%
FOR U = 1 TO MESSAGEPERPAGE
    IF POINT_VIEW.EOF THEN EXIT FOR
%>
        <tr>
            <td class="pointTabA"><%=POINT_VIEW("ADD_CAT")%></td>
            <td class="pointTabB"><%=POINT_VIEW("ADD_COM")%></td>
            <td class="pointTabC"><%=POINT_VIEW("ADD_POINTS")%></td>
        </tr>
<% POINT_VIEW.MOVENEXT
    NEXT%>
        <tr>
            <td height="100%"></td>
            <td height="100%"></td>
            <td height="100%"></td>
        </tr>
    </table>
    <table cellpadding="0" cellspacing="0" height="20%" width="100%">
        <tr>
            <td height="100%" width="100%" valign="bottom">
                <table cellpadding="0" cellspacing="0" height="100%" width="100%">
                    <tr>
                        <td width="70%"><a style="font: normal 9pt verdana; color: #666; text-decoration: underline; padding-left: 10" href="/mytravel/mypoints_account.asp">View All Activity</a></td>
                        <td width="15%"><div style="font: normal 9pt verdana; color: #666; padding-right: 10">Balance:</div></td>
                        <td width="15%"><div style="font: normal 11pt bookman old style; color: #666; text-align: center"><%=balance%></div></td>
                    </tr>
                </table>
            </td>
        </tr>
    </table>
<%END IF%>
</div>

You need to keep the running total *inside* the loop; not just once at the beginning. E.g.:

Do While Not MyRecordset.EOF
  ' Present your individual rows here.

  Total = Total + MyRecordset("Points")
  MyRecordset.MoveNext

Loop

Your code uses quite a different approach to loop the data, but you can use the same idea. Just before:

<% POINT_VIEW.MOVENEXT

add:

balance = balance + Int(POINT_VIEW("ADD_POINTS"))

So you end up with something like:

            <td class="pointTabC"><%=POINT_VIEW("ADD_POINTS")%></td>
        </tr>
balance = balance + Int(POINT_VIEW("ADD_POINTS"))
<% POINT_VIEW.MOVENEXT

After the last loop, balance should contain the total number of points.

Doug: to use SUM, don't you need a GROUP BY clause on the user's name?

Imar


---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

Imar: It depends. Consider this code:
declare @tbl table (value int, userID int)
INSERT INTO @tbl (value, userID)VALUES(10, 1)
INSERT INTO @tbl (value, userID)VALUES(10, 1)
INSERT INTO @tbl (value, userID)VALUES(10, 1)
INSERT INTO @tbl (value, userID)VALUES(10, 1)
INSERT INTO @tbl (value, userID)VALUES(10, 2)
INSERT INTO @tbl (value, userID)VALUES(10, 2)

SELECT sum(value), userID from @tbl where userID = 1

this will return you a resultset of 40 if, however, you do:
SELECT sum(value), userID from @tbl group by userID

Your resultset will be
Value UserID
40 1
20 2

You only need a group by clause if you are selecting data from a column and you are not using an aggregate function on it, so in my last examle if i were to have done

SELECT sum(value), userID from @tbl where userID = 1

this would have generated a SQL error but simply having SELECT Sum(value) without a group by clause is perfectly legal.

kj, i completely understand that the data will not be known at runtime, i was using the code i provided (using the values you had provided) to illustrate how this would be done in SQL. You could potentially have 1000 rows of data for one user and calling Sum() will return you the sum of all values in that column for that user.



================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========