DSUM function

[Steven]

Is there a way to order the table when using the DSUM function to make it
process faster.

For example I am doing:
vTestBalance = ("Amount", "tblActivity001", "Period Between '" & vsYear & "'
AND '" & vsYear1 & "'")

Can you use something like an ORDER BY clause to order the Periods to make
it work faster. I am trying different things like what I have below but I
cant make it work.
vTestBalance = ("Amount", "tblActivity001", "Period Between '" & vsYear & "'
AND '" & vsYear1 & "'","ORDER BY [Period]")

Is there a way to do this for a faster return?

Thanks,

Steven

 

[David C. Holley]

Pretty much - No. The only thing that you can do is to create an Index

 

[Tim Ferguson]

Can you use something like an ORDER BY clause to order the Periods to
make it work faster.

As long as there is an index on Periods, then the Rushmore technology
should be able to identify it and use it as required.

In general, Jet is pretty good at optimising queries, but you don't get
to see how it does it, nor do you get much opportunity to affect it
anyway.

At bottom, you may just want to try some benchmarking of things like

SELECT SUM(Amount) FROM tblActivity001
WHERE Period BETWEEN 1970 AND 1999

or whatever. By the way, as long as Period is numeric, you don't need the
quotes round the numbers, and it's my guess that the query will be a bit
quicker if you take them out, so that the db engine does not have to
coerce the strings into integers.

Or try...

WHERE 1970 < Period AND Period < 2000

etc etc.

Best wishes

Tim F