Opinion about aggregates

AccessKay wrote:

I’m fairly new to Access so this may be an inane question to all those
experienced people out there. My question is about the use of aggregates
in
a query. I’m noticing that designing queries with aggregates might lead
to
problems. Is it best to not build queries with aggregates if you can
avoid it?

Thanks for any feedback.

If you mean the "domain aggregate functions" e.g. DLookup(), DSum(),
DCount(), etc., then yes they should be avoided whenever possible in
queries.

The reason is that these functions all have a bit of resource overhead that
is consumed for each call. That means calling them in queries or code
looping operations results in paying that overhead over and over and over.

The normal aggregate functions like Sum(), Count(), Min(), etc., are
perfectly fine in queries. Databases would be pretty useless without them.

I’m fairly new to Access so this may be an inane question to all those
experienced people out there. My question is about the use of aggregates in
a query. I’m noticing that designing queries with aggregates might lead to
problems. Is it best to not build queries with aggregates if you can avoid
it?

Thanks for any feedback.

If you are talking about things like DCount, DMax, and such, they can be
really slow if you have a lot (like 50,000+) records. I tend to avoid them if
possible.

On the other hand, Totals queries are very useful to summerize data.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"AccessKay" wrote:

I’m fairly new to Access so this may be an inane question to all those
experienced people out there. My question is about the use of aggregates in
a query. I’m noticing that designing queries with aggregates might lead to
problems. Is it best to not build queries with aggregates if you can avoid
it?

Thanks for any feedback.

Thanks. I’m going to take it as a rule to avoid using domain aggregate
functions if at all possible. Though not sure yet as to how to workaround it
but I still have lots more to learn.

"Jerry Whittle" wrote:

If you are talking about things like DCount, DMax, and such, they can be
really slow if you have a lot (like 50,000+) records. I tend to avoid them if
possible.

On the other hand, Totals queries are very useful to summerize data.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"AccessKay" wrote:

I’m fairly new to Access so this may be an inane question to all those
experienced people out there. My question is about the use of aggregates in
a query. I’m noticing that designing queries with aggregates might lead to
problems. Is it best to not build queries with aggregates if you can avoid
it?

Thanks for any feedback.

When you get around to it, the alternative is to use correlated
subqueries. I'm not going to go into a lot of detail here because it
sounds like you're not ready to use them yet. You can look up the topic
about subqueries in online help when the time comes so you'll have a
starting point.

AccessKay wrote:
Thanks. I'm going to take it as a rule to avoid using domain
aggregate functions if at all possible. Though not sure yet as to
how to workaround it but I still have lots more to learn.

"Jerry Whittle" wrote:

If you are talking about things like DCount, DMax, and such, they
can be really slow if you have a lot (like 50,000+) records. I tend
to avoid them if possible.

On the other hand, Totals queries are very useful to summerize data.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"AccessKay" wrote:

I'm fairly new to Access so this may be an inane question to all
those experienced people out there. My question is about the use
of aggregates in a query. I'm noticing that designing queries with
aggregates might lead to problems. Is it best to not build queries
with aggregates if you can avoid it?

Thanks for any feedback.

--
HTH,
Bob Barrows

It depends. I do tend to avoid them in queries for performance reasons. BUT
if I know that the query needs to be updateable and I know that there will be
a limited number of records returned I will use the aggregate functions.
However, I never apply criteria against an aggregate function in a query.
That could lead to the query engine doing the calculation for EVERY record in
the table in order to apply the filter.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

AccessKay wrote:
I’m fairly new to Access so this may be an inane question to all those
experienced people out there. My question is about the use of aggregates in
a query. I’m noticing that designing queries with aggregates might lead to
problems. Is it best to not build queries with aggregates if you can avoid
it?

Thanks for any feedback.

Thanks Bob for letting me know about correlated subqueries. Thanks also John
for your insight about when you might make an exception to the rule.

I appreciate all the excellent feedback from all!


"John Spencer" wrote:

It depends. I do tend to avoid them in queries for performance reasons. BUT
if I know that the query needs to be updateable and I know that there will be
a limited number of records returned I will use the aggregate functions.
However, I never apply criteria against an aggregate function in a query.
That could lead to the query engine doing the calculation for EVERY record in
the table in order to apply the filter.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

AccessKay wrote:
I’m fairly new to Access so this may be an inane question to all those
experienced people out there. My question is about the use of aggregates in
a query. I’m noticing that designing queries with aggregates might lead to
problems. Is it best to not build queries with aggregates if you can avoid
it?

Thanks for any feedback.

.

On Tue, 13 Apr 2010 07:25:01 -0700, AccessKay
wrote:

I’m fairly new to Access so this may be an inane question to all those
experienced people out there. My question is about the use of aggregates in
a query. I’m noticing that designing queries with aggregates might lead to
problems. Is it best to not build queries with aggregates if you can avoid
it?

Thanks for any feedback.

Ummm...

Is it best to use a woodworking shop without using the table saw? Table saws
can be dangerous!

Of course you should use aggregate queries, when an aggregate query is the
tool to do what you need done. Sure, they can be used inappropriately, and you
have to understand how they work; but that's no reason not to use them. They
exist to be used!
--

John W. Vinson [MVP]

Aggregate queries use things like MAX, MIN, SUM, FIRST.
Using queries with aggregates might lead to problems: for this
reason it is sometimes better to use the Domain Aggregate
Functions (things like DMAX, DMIN, DSUM, DLOOKUP).

Domain Aggregate Functions are slower, but they avoid some
of the problems with Aggregate Queries and Correlated
Subqueries.

The problem with Correlated Subqueries is mostly that
they are a bit slow, a bit tricky, and a bit difficult to set up.

The problem with Aggregate queries is that they turn your
select queries into read-only, and sometimes they fail or
give the wrong result if they are very complex.

The problem with Domain Aggregate Functions is that they
are slow, and require tables to be in the Current Database,
(which is only a problem if you are using references, or
querying an unlinked database).

So the answer is, you should only use queries which return
the maximum, minimum, average, sum, count or whatever
if you need a query to return the maximum, minimum, average,
sum, count or whatever, and when you do, you have a
choice of using a report, or three different query methods.

(david)

"AccessKay" wrote in message
...
I'm fairly new to Access so this may be an inane question to all those
experienced people out there. My question is about the use of aggregates
in
a query. I'm noticing that designing queries with aggregates might lead
to
problems. Is it best to not build queries with aggregates if you can
avoid
it?

Thanks for any feedback.