Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 25th, 2003, 09:39 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi judy,

I would guess that you get a different result because the TOP 78 is applied after the SUM, but the SUM only produces one record, so you are effectively summing all records that match the where criteria. I'll have a think about how to get around that and get back to you...
 
Old July 25th, 2003, 09:44 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

Judy, I don't know if you can do subqueries in Access, but if you can then that's your answer.

SELECT SUM(X.units_2) FROM
(SELECT TOP 78 units_2 FROM dbo_work_detail WHERE ...) AS X
 
Old July 25th, 2003, 10:06 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi again Judy,

I've been messing about with Access and you can do this subquery thing, but the syntax is a bit odd, it seems you need square brackets around the subquery, plus a . after it, like this:
Code:
SELECT SUM(X.units_2) FROM 
[SELECT TOP 78 units_2 FROM dbo_work_detail WHERE ...]. AS X
 
Old July 27th, 2003, 09:53 PM
Friend of Wrox
Points: 1,035, Level: 12
Points: 1,035, Level: 12 Points: 1,035, Level: 12 Points: 1,035, Level: 12
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: , , USA.
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to jmss66
Default

Hi Phil,

Thanks for experimenting. I was wondering too if I can do sub queries. I guess I don't have to wonder no more, thanks to you. I will try this and hope this would solve the problem. I am trying to make the program run faster. As it is, it runs slower than a turtle because of going through 78 records several times depending on how far back the date the user puts in. I will try this and let you know the outcome. Thanks again for helping.

Judy
 
Old July 28th, 2003, 09:30 AM
Friend of Wrox
Points: 1,035, Level: 12
Points: 1,035, Level: 12 Points: 1,035, Level: 12 Points: 1,035, Level: 12
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: , , USA.
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to jmss66
Default

Hi Phil,

This is the error message I am getting:
ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

/hfrrfnew/BackDropEstimate.asp, line 207

Line 207 is:

Reponse.write "dbltotal " & orswd("X") & "<BR>"

I tried to display the value of X after the query, then I get this error message. Where is the value stored after the query has been executed? I tried orswd("X.units_2"), still got the same error.

Thanks,
Judy
 
Old July 29th, 2003, 02:38 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi Judy,

Sorry, forgot to give the SUM() field an alias. Its probably returned as "Expr1" or something like that.

Try this amendment:
Code:
SELECT SUM(X.units_2) AS TotalPay FROM 
[SELECT TOP 78 units_2 FROM dbo_work_detail WHERE ...]. AS X
Then you can do Reponse.write "dbltotal " & orswd("TotalPay") & "<BR>"

rgds
Phil
 
Old July 29th, 2003, 07:53 AM
Friend of Wrox
Points: 1,035, Level: 12
Points: 1,035, Level: 12 Points: 1,035, Level: 12 Points: 1,035, Level: 12
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: , , USA.
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to jmss66
Default

Hi Phil,

It worked! Thank you so much for your help and patience. More power to you.

Judy
 
Old July 29th, 2003, 08:00 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hey Judy that's great. Thanks for letting me know it worked in the end.:)

rgds
Phil




Similar Threads
Thread Thread Starter Forum Replies Last Post
sum function pcase XSLT 2 January 2nd, 2008 05:51 PM
sum() function felixm_jr Reporting Services 1 April 22nd, 2007 01:59 AM
Xpath: sum function gracehanh XSLT 13 September 27th, 2005 09:30 AM
sum function problem sherr8 Access 1 February 13th, 2004 03:06 PM
Need Help with the Sum Function athanatos XSLT 1 July 22nd, 2003 10:06 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.