473,218 Members | 1,673 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,218 developers and data experts.

Aggregate Query Woes

32,554 Expert Mod 16PB
Introduction :

How many times have you come across the following error message when working within Aggregate, or Sum, queries?
Access Error:
You tried to execute a query that does not include the specified expression 'X' as part of an aggregate function.
What is this message relating to and how can one avoid it?

Aggregate (Sum) Functions :

These are the standard functions available to Jet SQL. I can't say no others can exist, nor that they may not be introduced later into Jet SQL itself, but these are currently available :
Sum(), Avg(), Min(), Max(), Count(), StDev(), Var(), First() and Last()

Converting a Query to Aggregate (or Sum) :

In SQL the GROUP BY clause can be used, but is actually not absolutely necessary. Any reference to an aggregate function within the SQL will cause the query to behave as an aggregate one. If no GROUP BY clause is included the whole dataset is treated as a single group.

In Design View select View | Totals, or click on the Sigma-shaped button (Like an 'M' rotated 90 degrees anti-clockwise), to change the options to allow selecting of aggregate functions and also fields to group by.

Access Help :

It may help to start with what Access Help has to say on the subject :
Jet SQL Help:
All fields in the SELECT field list must either be included in the GROUP BY clause or be included as arguments to an SQL aggregate function.
This quote from the Help system implies that all references to fields, even within compound references, must either be aggregated (IE. included in one of the aggregate functions listed above) or included in the GROUP BY clause. Any expression which is of either type is considered aggregated. The aggregate functions can only take field reference expressions which resolve to non-aggregated fields (IE. It is equally invalid to aggregate data more than once).

Explanation and Example Illustration :

Essentially, the SQL Engine needs to know how to process any particular reference. If you have a reference to a field which is not inside an aggregate function of any type, and is also not in the GROUP BY clause, then how is the SQL Engine to know which of the potentially many records in the group to take the value from.

See the situation below and ask yourself whether the result for [fB] should be "A", "B" or "C" :
Expand|Select|Wrap|Line Numbers
  1. SELECT   [fA]
  2.        , [fB]
  3.        , Sum([fC]) AS [SumC]
  4. FROM     [Tbl]
  5. GROUP BY [fA]
Expand|Select|Wrap|Line Numbers
  1. Table = [Tbl]
  2. [fA]  [fB]  [fC]
  3.  1    "A"    11
  4.  1    "B"    22
  5.  1    "C"    33
Result :
Expand|Select|Wrap|Line Numbers
  1. [fA]=1, [fB]="???", [SumC]=66
Conclusion :

The fact that you cannot determine from this information which item should be selected, is an illustration of why it doesn't make sense unless the above criteria are met (In short that all field references are aggregated in one way or another).
Mar 19 '12 #1
0 18621

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Toby Dann | last post by:
I have an aggregate query as recordsource for a form to show a list of invoices, including the totals (calculated by the aggregate query - I'm trying to duplicate as little info as possible here)....
by: sausage31 | last post by:
I have a table as follows.... Device LotID Result1 Result2 Result3 aaa 1 5 10 15 bbb 1 2 4 6 aaa 2 ...
by: neb | last post by:
Dear member of the forum, Ms access has built-in aggregate function like: -Sum, Max, First, Avg, ... Is it possible to build user-defined aggregate? (if you have any clue, do not hesitate to...
by: Scott Gerhardt | last post by:
Hello, I am new to the list, my apology if this question is beyond the scope or charter of this list. My questions is: What is the best method to perform an aggregate query to calculate sum()...
by: lrgclasper | last post by:
Greetings, I have the following select statement from a single table ( Haul number, weight, species code). I would like the output to provide me with hauls with only two species. When I use...
by: brent78 | last post by:
I'm trying to create a query that finds the max of a group of records and then displays all details about that record. Suppose this is my data: Type Size Color Quantity A L Green 5 A M Blue 7...
by: John | last post by:
Is there a way to create a grouping query and be allowed to edit the data. I want to create a query that has a grouping and totals but also allow the user to edit. Can it be done?
by: lorirobn | last post by:
Hi, I have a report that displays summary information, summing prices for all records for a RoomID meeting certain criteria, and printing the roomID and sum on a detail line. Now I want to add...
by: sbowden81 | last post by:
Hi all, I'm trying to create what I thought was a simple inventory query, but I'm having a problem. I have a shipment table that looks like this: SHIP_ID SHIP_QTY 1 24 2 ...
by: clickingwires | last post by:
How do you consecutively number rows in an aggregate query?
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.