Subquery One Table Multiple Fields

[Du2good]

Still learning about subquery design. I've been researching this, and have gotten to this point, unfortunately still have much to learn about this subject
Trying to get count of Acc grouped by Stats that have a balance > 0
and count of Acc grouped by Stats that have a balance <= 0 in the same query.
When I run the SQL it prompts for parameter for A.Acc, A.Stats etc.

SELECT 
          A.Acc, 
          A.Stats, 
          A.Bal, 
          B.Acc,
          B.Stats, 
          B.Bal
FROM [SELECT Count(Acc) AS CntAccLessZero,
           Sum(Bal) AS SumBalLessZero
         FROM tblAccount
         GROUP BY Stats, Bal 
       ]. AS A, 

        [SELECT Count(Acc) AS CntAccGreaterZero,
         Sum(Bal) AS SumBalGreaterZero
         FROM tblAccount
         GROUP BY Stats, Bal
       ]. AS B
WHERE (((A.Bal)<=[O]) AND ((B.Bal)>0));

Thank you for the assistance.

 

[lameid]

Fields/columns in the main or top query must be returned or exist some place in something returned in the from clause. That is a field in a table OR something in the SELECT of a subquery. Access thinks anything that doesn't exist is a parameter and prompts for it...

Other things that jump out at me...


Subqueries are not delimited with Square brackets but parenthesis... also there is no dot (although there are odd syntax variations that work... so maybe)...
Secondly each sub query is a full select unto itself and the criteria belongs in each of them... However you want answers based on aggregates which means the Having Clause instead of a where clause if I understand your intent correctly.
You don't want to group on Bal as you want to sum on it...

All that, without some sample data and sample output is hard to be sure that is all the issues.

Generally I would go about this a little differently but the direction you are taking I think should work....

Having said that my approach would be...
I'd group on stats, count accounts and sum the balance in a sub query. In the main query I would use IIF to find the balances lesst than 0, return the count determined in the sub query and 0 otherwise... sum that. That is your count... repeat for greater than.