Domain Aggregate Functions

[David M C]

Having read:

http://www.access.qbuilt.com/html/multiuser_applications.html

It suggests not to use Domain Aggregate Functions in a multi-user
environment. I use DLookup's and DSum's quite a lot. What would be the
alternative to these?

Thanks,,

Dave

 

[Jeff Boyce]

David

?Your source doesn't offer an alternative?

Another approach might be to create queries that "lookup" and "sum".


--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[BruceM]

The link is an article by Tom Wickerath. It contains a lot of really
helpful stuff (I bookmarked it), but the recommendation about domain
aggregate functions sort of leaves the reader hanging.

 

[David M C]

So, is having a Totals query do a Sum faster than using a DSum, when both the
DSum and the query use the same criteria?

Dave

 

[Jeff Boyce]

David

In what environment? You've asked a general question, but not provided the
context ...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Dirk Goldgar]

Having read:

http://www.access.qbuilt.com/html/multiuser_applications.html

It suggests not to use Domain Aggregate Functions in a multi-user
environment. I use DLookup's and DSum's quite a lot. What would be the
alternative to these?

What Tom says is,

<QUOTE>
In queries that involve linked tables, avoid using VBA functions in
query criteria, especially domain aggregate functions, such as DSum,
anywhere in your queries. When you use a Domain Aggregate Function,
Access may retrieve all of the data in the linked table to execute the
query.

</QUOTE>

Note that he's specifically talking about using these functions *in
queries*. I don't believe he's talking about not using the functions in
form code, module code, etc., at all. What he's cautioning against, I
believe, is writing queries that use domain aggregate functions in
calculated fields and criteria. For example, a query like this:

SELECT Field1, Field2 FROM MyTable
WHERE Field2 = DMax("Field2", "MyTable");

will be extremely inefficient.

 

[David M C]

My database is a split frontend/backend, with the backend on a server share,
and the frontend on each workstation.

Imagine a query with 1000 or so price (currency) fields that need to be
summed based on 6 different sets of criteria (therefore giving 6 individual
sums) for display on a single form. Would it be quicker to do this using 6
different fields in a query, or using 6 different DSums?

To add a level of complexity, the query the form is based on, or the query
the DSum uses has to change at runtime (based on criteria on another form).
I currently do the summing using a DSum and everything works, but the form
can take 3-4 seconds to load.

Dave

 

[BruceM]

The section you quoted appears under the heading:

Avoid using Domain Aggregate Functions

This is followed by:

"Domain Aggregate Functions are used to look up a value or calculate
statistics within a set of records -- a domain. These include DCount,
DLookup, DMax, DMin, DSum, DAvg, DFirst, DLast, DStDev, DStDevP, DVar, and
DVarP, all of which may adversely affect performance."

Then there is the line you quoted. The intention seems to be to suggest
avoiding these functions in query criteria when linked tables are involved,
but it may appear at first to be a suggestion that domain aggregate
functions not be used at all.

 

[Joan Wild]

There are alternatives at www.mvps.org/access. Look for Domain Aggregate
Functions Replacements in the Modules section.

 

[Jeff Boyce]

If the form is taking that long to load, are you loading a large number of
records? Could you start the form with no records, and let the user select
one for display?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[David M C]

The form is a popup. The Sums it does are based on criteria from the calling
form, so the minimum number of records for the form are already selected. The
sum can work on a range of approximately 10 - 1000 records, depending on the
criteria. I just wanted to know if a DSum was faster or slower than using a
totals query? Given the same set of records needing summing.

Thanks,

Dave

 

[Rick Brandt]

The form is a popup. The Sums it does are based on criteria from the
calling form, so the minimum number of records for the form are
already selected. The sum can work on a range of approximately 10 -
1000 records, depending on the criteria. I just wanted to know if a
DSum was faster or slower than using a totals query? Given the same
set of records needing summing.

Since the query does not need to instantiate a database object it would be
faster, but in the case of a single function call I doubt that the user would be
able to perceive the difference.

When in doubt, test both.