Hi
I am trying to create a subquery which will calculate the sum of a field 
using  GROUP BY ie:

AcTotal: (SELECT Sum([NowValue]) FROM Holdings GROUP BY Holdings.Acc)


All I get is an error message saying "At most one record can be returned 
by this subquery"

Any ideas?

without seeing the entire query, this is just a guess...

 (SELECT Holdings.Acc, Sum([NowValue]) FROM Holdings GROUP BY Holdings.Acc)

can you post the entire SQL statement you are trying to write?  Then maybe I
can help more.

David


-----Original Message-----
From: Liz Haskin [mailto:liz@t...]
Sent: Thursday, April 18, 2002 9:30 AM
To: Access
Subject: [access] Group By in Subquery


Hi
I am trying to create a subquery which will calculate the sum of a field 
using  GROUP BY ie:

AcTotal: (SELECT Sum([NowValue]) FROM Holdings GROUP BY Holdings.Acc)


All I get is an error message saying "At most one record can be returned 
by this subquery"

Any ideas?

Hi David
Here is the SQL stm

SELECT ContactQuery.ContactID, ContactQuery.Client, 
ContactQuery.MandateID, ContactQuery.MandateName, 
ContactQuery.AccountNumber, ContactQuery.Acct, ContactQuery.Broker, 
Holdings.NowValue, (SELECT Sum([NowValue]) FROM Holdings GROUP BY 
Holdings.Acc) AS AcTotal
FROM ContactQuery LEFT JOIN Holdings ON ContactQuery.Acct = Holdings.Acc;

I tried what you suggested but it then it asks me to use the EXISTS 
reserved word.
thanks 
liz

> 
without seeing the entire query, this is just a guess...

 (SELECT Holdings.Acc, Sum([NowValue]) FROM Holdings GROUP BY Holdings.Acc)

can you post the entire SQL statement you are trying to write?  Then maybe 
I
can help more.

David


-----Original Message-----
From: Liz Haskin [mailto:liz@t...]
Sent: Thursday, April 18, 2002 9:30 AM
To: Access
Subject: [access] Group By in Subquery


Hi
I am trying to create a subquery which will calculate the sum of a field 
using  GROUP BY ie:

AcTotal: (SELECT Sum([NowValue]) FROM Holdings GROUP BY Holdings.Acc)


All I get is an error message saying "At most one record can be returned 
by this subquery"

Any ideas?

I would try this : create a seperate query Q1

SELECT Holdings.Acc, Sum([NowValue]) AS AcTotal FROM Holdings GROUP BY
Holdings.Acc 

Then use that in the main query

SELECT ContactQuery.ContactID, ContactQuery.Client, 
ContactQuery.MandateID, ContactQuery.MandateName, 
ContactQuery.AccountNumber, ContactQuery.Acct, ContactQuery.Broker, 
Holdings.NowValue, Q1.AcTotal
FROM ContactQuery LEFT JOIN Holdings ON ContactQuery.Acct = Holdings.Acc
LEFT JOIN Q1 ON ContactQuery.Acct = Q1.Acc;


if your data has multiple values in Holdings for each Acc value, you will
have the same contactID multiple times, one for each Holdings record having
that value of Acc, and each of those records will have the same AcTotal and
possibly different NowValue.  That is, if contact #1 has 4 records in the
holdings table, thee will be 4 records in the result of this query each
having contact #1, each having the same AcTotal, and having the 4 distinct
NowValue values from the 4 holdings records.

This is the way you originally wrote it, not because of my suggestion :)  is
this what you intended?

If there is only one Holdings record per Acc, then you don;t need to do a
sub-query total at all.

If you want one record per contact you have to get rid of NowValue.





-----Original Message-----
From: Liz Haskin [mailto:liz@t...]
Sent: Friday, April 19, 2002 5:00 AM
To: Access
Subject: [access] RE: Group By in Subquery


Hi David
Here is the SQL stm

SELECT ContactQuery.ContactID, ContactQuery.Client, 
ContactQuery.MandateID, ContactQuery.MandateName, 
ContactQuery.AccountNumber, ContactQuery.Acct, ContactQuery.Broker, 
Holdings.NowValue, (SELECT Sum([NowValue]) FROM Holdings GROUP BY 
Holdings.Acc) AS AcTotal
FROM ContactQuery LEFT JOIN Holdings ON ContactQuery.Acct = Holdings.Acc;

I tried what you suggested but it then it asks me to use the EXISTS 
reserved word.
thanks 
liz

> 
without seeing the entire query, this is just a guess...

 (SELECT Holdings.Acc, Sum([NowValue]) FROM Holdings GROUP BY Holdings.Acc)

can you post the entire SQL statement you are trying to write?  Then maybe 
I
can help more.

David


-----Original Message-----
From: Liz Haskin [mailto:liz@t...]
Sent: Thursday, April 18, 2002 9:30 AM
To: Access
Subject: [access] Group By in Subquery


Hi
I am trying to create a subquery which will calculate the sum of a field 
using  GROUP BY ie:

AcTotal: (SELECT Sum([NowValue]) FROM Holdings GROUP BY Holdings.Acc)


All I get is an error message saying "At most one record can be returned 
by this subquery"

Any ideas?