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.

 

[Clifford Bass via AccessMonster.com]

Hi Hadi,

Have you tried just using =Count(*) as the Data Source of the text box
in the first grouping? Or am I missing something about what you are trying
to do? If so, please expand, maybe with an example.

Clifford Bass

 

[Hadi]

Hi Clifford,

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

Hadi

 

[Clifford Bass via AccessMonster.com]

Hi Hadi,

If Dale's solution does not help... I am clearly confused as to your
goal. What precisely are you counting? The number of groups? Again, an
actual small example would help.

Clifford Bass