Group By... Another Problem

[Marty]

I received an error: "You tried to execute a query that
does not include the specified expression 'Field1' as part
of an aggregate function". This is the query I wrote.

SELECT Max(Table1.field3) AS Maxof,
Table1.Field1, Table1.Field2,
Table1.Field4, Table1.Field5,
Table1.Field6
FROM Table1
GROUP BY Table1.Field5, Table1.Field6;

Saved query as q1 then:

SELECT Table1.*
FROM Table1 INNER JOIN q1
ON Table1.field5 =q1.field5
AND Table1.field6 = q1.field6
AND Table1.field3 = q1.maxOf

Any suggestions?

Thank you, Marty

 

[Marty]

For the benifit of those present and future, I received
this answer from another ACCESS forum. You are all a
great help. I don't no what I'd do without you. Thank
you.

sbaxter replied:

You need to specify how you want you want to do with
Fields 2,3,4 in your grouping

SELECT Table1.Field5, Table1.Field6, Max(Table1.Field1) AS
MaxOfField1, First(Table1.Field2) AS FirstOfField2, First
(Table1.Field3) AS FirstOfField3, First(Table1.Field4) AS
FirstOfField4
FROM Table1
GROUP BY Table1.Field5, Table1.Field6;

 

[Michel Walsh]

Hi,


Since you GroupBy Field5 and Field6, any other field in the SELECT need to
be aggregated. Field1, Field2 and Field4 are not, REMOVE THEM, they are not
needed there, anyhow, in this case.



Vanderghast, Access MVP