How do I count subrecords returned from a query?

[Phil Davis]

What started as a simple requirement is turning into something rather more complex. In the following (simplified) query:

Select field1, field2
FROM table
WHERE ....
GROUP BY field1, field2
 

Which produces a result along the lines:

field1  field2
aa    1
aa    6    
aa    1000
bb    10    
bb    25
 

How do I include a count of the number of aa and bb records produced as part of the rows returned?

I tried using count(field1) but the result returns the count of field1/field2 rows (ie 1 in each of the above cases).

I found a convoluted way by outputing the results into a result table using SELECT INTO and running a seperate query to determine the counts but then found this did not work when I tried to open a recordset in VBA on the SELECT INTO (I get error number 3219: Operation Invalid).

So I then tried to use OpenQuery to create the results table. However, I think I have learned from these pages that I can't run a stored action query from VBA using the OpenQuery method and pass parameters to it in the same way as I pass parameters to a stored "non action" query when opening a recordset, so I've ended up with something like:

Select field1, count(field1)
From (Select field1, field2
FROM table
WHERE ....
GROUP BY field1, field2)
 

Which seems to work but I then have to generate two recordsets in the VBA to achieve what I set out to do. It feels overly complicate !!

[Stewart Ross]

What you've arrived at using a subquery is correct, and is not over-complicated really.

There are two distinct levels of aggregation involved: aggregation by field 1 along with field 2 (the example you show), and aggregation by field 1 alone. The set of records returned by the field 1 and field 2 grouping is a subset of that for field 1 alone, so there is no single-query way within that subset to count the larger set from which it was taken.

In summary, a two-query or query/subquery approach is correct for this kind of multiple aggregation requirement, so well-done for coming up with a good solution!

-Stewart