Aggregate function

Table (Field1, Field2)

a = "SELECT SUM(T.Field2) AS F FROM Table AS T WHERE T.field1=1"
Set rs = db.OpenRecordset(a)
It works.

but
a = "SELECT T.field1, SUM(T.Field2) AS F FROM Table AS T WHERE T.field1=1"
Set rs = db.OpenRecordset(a)
It doesn't work.

Run-time error '3122': You tried to execute a query that does not include the specified expresion 'field1' as part of an aggregate function.)

Try using group by as in:

a = "SELECT T.field1, SUM(T.Field2) AS F FROM Table AS T WHERE
T.field1=1 group by T.Field1"
....
--
HTHs Martin


"stefantem" wrote:

>
> Table (Field1, Field2)
>
> a = "SELECT SUM(T.Field2) AS F FROM Table AS T WHERE T.field1=1"
> Set rs = db.OpenRecordset(a)
> It works.
>
> but
> a = "SELECT T.field1, SUM(T.Field2) AS F FROM Table AS T WHERE
> T.field1=1"
> Set rs = db.OpenRecordset(a)
> It doesn't work.
>
> Run-time error '3122': You tried to execute a query that does not
> include the specified expresion 'field1' as part of an aggregate
> function.)
>
>
> --
> stefantem
> ------------------------------------------------------------------------
> stefantem's Profile: http://www.excelforum.com/member.php...o&userid=13594
> View this thread: http://www.excelforum.com/showthread...hreadid=502805
>
>

It works. Thanks.