Count Property in Aggregate Query

[MTrem]

Counting rows in a Query

I have an aggregate query that creates a report of "A" by "B". Each "A" can have many "B"s under it. The report works fine. I need to count and display on the report the number of "B"s for each "A". I can't seem to manipulate the "count" property of the query to generate the correct number. Little help??? Thanks....

Mike T

 

[mresann]

Can you post the query? I am guessing you may need subqueries, but wont' know until I see the post.

 

[MTrem]

Here is the SQL. I don'tknow how to post the actual query. I hope this will do.
I need to count the number of [ERClient.Number] that falls under each [ERSubmitter.[Sbm Key]] within a date range. The date range parameters work fine.


SELECT ERSubmitter.[Sbm Key], ERSubmitter.[Sbm Name], ERClient.Number, ERClient.[Last Name], ERClient.[First Name], ERClient.MI, ERClient.[Reg Date], [Start Date] AS StDate, [End Date] AS EnDate
FROM ERSubmitter LEFT JOIN ERClient ON ERSubmitter.[Sbm Key] = ERClient.[Submitter Key]
GROUP BY ERSubmitter.[Sbm Key], ERSubmitter.[Sbm Name], ERClient.Number, ERClient.[Last Name], ERClient.[First Name], ERClient.MI, ERClient.[Reg Date], [Start Date], [End Date]
HAVING (((ERClient.[Reg Date])>=[Start Date] And (ERClient.[Reg Date])<=[End Date]));

 

[mresann]

I took out a lot of the irrevelent fields to make this query.

SELECT ERSubmitter.[Sbm Key], Count(ERClient.Number) AS CountOfNumber
FROM ERSubmitter LEFT JOIN ERClient ON ERSubmitter.[Sbm Key] = ERClient.[Submitter Key]
WHERE (((ERClient.[Reg Date])>=[Start Date] And (ERClient.[Reg Date])<=[End Date]))
GROUP BY ERSubmitter.[Sbm Key];

Once you review this query in design view, you can re-add the fields you need.

 

[MTrem]

Thank you.. It works fine now! I couldn't get the count to work because I had too many group bys. Your SQL works fine for getting the count, but if I add the other fields needed on the report it corrupts the ability to get the count I needed. If I wasn't shown by a colleague how to use a select query for gathering the data and then doing the grouping in the report itself, I would have used your query to generate the counts and then call that query into a new query that would display the proper fields on the report. Thanks for your time and effort.

Mike

 

[gblack]

Report

If you actually mean an Access report its pretty easy.

First I'd change your query to a simple select statement without grouping any of your data...unless all you want is the counts.

Use the report GUI to get all of your counts (via its sorting and grouping function). Choose your "A" field as a group.... (select "yes" for group footer, so you can get your "B" counts).

Then you can get a "B" count by using the count function... Count()... within the group footer.

You do this by making an unbound Text Box in the footer. In the text box you should put: = Count()

HTH
Gary