By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,503 Members | 2,788 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,503 IT Pros & Developers. It's quick & easy.

DSum & DCount -> SQL

P: n/a
I have read a few articles that state that a multi-user app over a
network will run faster if DSum & DCount functions are replaced with
SQL statements replicating the functions. As I am a novice to SQL, any
suggestions would be hugely appreciated. Thanks.

Apr 26 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
If you have a single lookup taking place, e.g. in the Report Footer section,
or on a Form view form, the issue is purely academic. Just go ahead and use
the domain aggregate function.

If you have multiple lookups, you might consider opening a recordset to
retrieve all the values at once, instead of multiple lookups.
OpenRecordset() will be faster than the domain aggregate functions. There's
an example of how to replace DLookup() in this article:
http://allenbrowne.com/ser-42.html

If you are doing this for every records (e.g. in a query, a continuous form,
or a report), opening a recordset for every record will still be cumbersome.
You might be able to circumvent this by creating a query that includes your
main table and also a lookup table, so the output has access to the lookup
table without needing and DLookup() or OpenRecordset(). And the query may
still be updatable (depending on what other tables, joins, and clauses the
query contains.

If it is okay not to have an updatable query, a subquery is another option.
This is enormously faster than DLookup() or OpenRecordset(), and allows you
to get calculated results from related tables without duplicating records.
If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
The major limitation of this approach is that if you are feeding a report
that performs any sorting or grouping on these fields, you are likely to run
into the "Multi-level group-by not allowed" error.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Sheldon Mopes" <Sh**********@KidNet.com> wrote in message
news:44501d4b.4092328@news-server...
I have read a few articles that state that a multi-user app over a
network will run faster if DSum & DCount functions are replaced with
SQL statements replicating the functions. As I am a novice to SQL, any
suggestions would be hugely appreciated. Thanks.

Apr 26 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.