Opinion about aggregates
in [Queries]
|
From: John Spencer on 13 Apr 2010 12:12 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. >
From: AccessKay on 13 Apr 2010 12:52 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. > > > . >
From: John W. Vinson on 13 Apr 2010 14:46 On Tue, 13 Apr 2010 07:25:01 -0700, AccessKay <AccessKay(a)discussions.microsoft.com> 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]
From: david on 14 Apr 2010 01:47
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" <AccessKay(a)discussions.microsoft.com> wrote in message news:A3631969-4C88-4FBE-B0AE-7B59D7305285(a)microsoft.com... > 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. > |