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.)


--
stefantem
------------------------------------------------------------------------
stefantem's Profile: http://www.excelforum.com/member.php...o&userid=13594
View this thread: http://www.excelforum.com/showthread...hreadid=502805

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

Aggregate function
 

It works. Thanks.


--
stefantem
------------------------------------------------------------------------
stefantem's Profile: http://www.excelforum.com/member.php...o&userid=13594
View this thread: http://www.excelforum.com/showthread...hreadid=502805