Tutorial :Why is my query demanding more fields in my Group By?



Question:

Problem:
I originally had a query that was working great but I'm now having to change it to pull more fields. When I try run the new query it picks a field name and says that I haven't included it as part of the aggregate function. Each time I get this error I can add the field the error specifies to the Group By statement and the error message will choose a new field that isn't included. Anyone have any idea's as to how I can get the same information I was getting with the original query just with more fields?

Description of how query is supposed to work:
The query is meant to pull one record for each distinct set of readings_miu_ids and ReadDates (The PremID field is the same for each distinct readings_miu_id).

Original Query:

strSql3 = " SELECT Distinct readings_miu_id, ReadDate, PremID " & _            "INTO analyzedCopy2 " & _            "FROM analyzedCopy "    DoCmd.SetWarnings False  DoCmd.RunSQL strSql3  DoCmd.SetWarnings True  

New Query:

strSql3 = " SELECT Top 1 readings_miu_id, Reading, ReadDate,Format([MIUtime],'hh:mm:ss') AS ReadTime,MIUwindow,SN,Noise,RSSI,ColRSSI,MIURSSI,Firmware,CFGDate,FreqCorr,Active,MeterType,OriginCol,ColID,Ownage,SiteID,PremID , Neptune_prem.prem_group1, Neptune_prem.prem_group2,ReadID " & _            "INTO analyzedCopy2 " & _            "FROM analyzedCopy " & _            "Group By readings_miu_id, ReadDate, PremID  " & _            "Order By readings_miu_id, ReadDate, ReadID, PremID "    DoCmd.SetWarnings False  DoCmd.RunSQL strSql3  DoCmd.SetWarnings True  


Solution:1

When you include a GROUP BY clause, each field must either be in the GROUP BY or have an aggregate function (e.g, MAX, MIN, SUM, COUNT) applied to it.

For example, a simple correct implementation might be:

SELECT Department, MAX( Salary ) FROM Employees GROUP BY Department

... and a simple incorrect implementation would be:

SELECT Department, Salary FROM Employees GROUP BY Department.

Consider the two statements above. For the first, you can easily imagine what a datasource would look like and what would be returned. However for the second, what would you return? Which individual value of Salary would you return in your resultset? Hence, when you group fields, each field in the result set must either participate in the GROUPing or be the result of an aggregation of the values collected from the group comprised of the other fields.


Solution:2

In my experience (which is only moderate) every column in the result set (but NOT every aggregate) must be in the group by.

Here's a decent reference


Solution:3

You can accomplish this via a subquery or two queries. Also, "CurrentDb.Execute" is the preferred method to run a query like this (instead of "DoCmd.RunSQL").

CurrentDb.Execute strSQL3, dbFailOnError  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »