From: AccessKay on
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.

From: Jerry Whittle on
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.
>
From: Rick Brandt on
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.

From: AccessKay on
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.
> >
From: Bob Barrows on
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