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_id*s 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

Answer 1

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

Answer 2

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.

Answer 3

Hi Bryan,

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

Hope this helps,

Robert