Group Count in Parent Group Header

[Hadi]

Hello Experts,

I know this has been asked several times on this forum. I have two groupings
in my the report. i want the Header of the first grouping to show the count
of records in the second groupings. I'm familiar with the trick where you
create a text box, set it to 1, set running sum to over group etc. This works
except i want to show the totals in the Header of the Group not the Footer.

Thanks

Hadi

 

[Dale Fye]

A couple of possible solutions.

1. Create a query that groups by the same field used in the first group and
Counts the # of elements in the second group for each

SELECT [Group1Field], Count([Group2Field]) as CountOfGroup2
FROM your Table
GROUP BY [Group1Field]

Then add this query into the query which the report is based upon, joining
it on the Group1Field. If you do this, you could just add a control for
CountOfGroup2 to your main forms Group#1 header.

2. An alternative would be to create an unbound textbox and use the DCOUNT(
) function to count the number of records that match the Group1 header.
Place the DCOUNT( ) function in the textboxes control source, like:

Control Source: =DCOUNT("SomeField", "SomeTableOrQuery", "[Group1Field] = "
& me.txtGroup1Field)

3. You could also use the FORMAT event of the Group#1 header to fill in a
textbox similar to option #2.

 

[Hadi]

Hi Clifford,

using the count(*) will count all of the records under the details section
which i really do not need

Hadi