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...

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

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:

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

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

Hi Judy,

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

Try this amendment:
Then you can do Reponse.write "dbltotal " & orswd("TotalPay") & "<BR>"

rgds
Phil

Hi Phil,

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

Judy

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

rgds
Phil