Multi-level record counts

[Leif]

I have a report that has three groups and a detail level.

I'm interested in displaying a count of entries from the third level group
at the end of the first and second group. For example,

Group 1
Group 2
Group 3
Detail
Group 2 Footer - Count of Group 3 records
Group 1 Footer - Count of Group 3 records

I have no problem in getting the group 2 footer. I just create a text box
with a control source of =1 in group 3, and running total set to yes, per
Access help instructions.

However, I've not been able to get a count of group 3 records at the group 1
level. I've tried using =sum([group 2 count]), however, that does not work.
It seems sum can only be used on database fields, not text box values. I've
also tried setting up a second text box within group 3, but it only returns
the same value as in the group 2 footer just before the group 1 break.

I also have a second question. I have a after section break on group 2.
That is OK, except when there is a group 1 break, in which case I do not want
the group 2 page break. The reason is to get both totals on the same page on
a group 1 break (assuming I can get that part to work).

Thanks,
Leif

 

[Allen Browne]

So in the Group 3 Footer section, you have a text box with properties like
this:
Control Source =1
Running Sum Over Group
Name txtGroup3Count
Format General Number

Now you want to transfer that up a level, so in the Group 2 Footer you will
need a text box with properties:
Control Source =[txtGroup3Count]
Running Sum Over Group
Format General Number

 

[Leif]

Thanks, that works great.

Regarding my second question, is there a way to suppress my page break on
group level 2 when I have a group level 1 break?

Thanks again for your help.

Regards,
Leif

So in the Group 3 Footer section, you have a text box with properties like
this:
Control Source =1
Running Sum Over Group
Name txtGroup3Count
Format General Number

Now you want to transfer that up a level, so in the Group 2 Footer you will
need a text box with properties:
Control Source =[txtGroup3Count]
Running Sum Over Group
Format General Number

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a report that has three groups and a detail level.

I'm interested in displaying a count of entries from the third level group
at the end of the first and second group. For example,

Group 1
Group 2
Group 3
Detail
Group 2 Footer - Count of Group 3 records
Group 1 Footer - Count of Group 3 records

I have no problem in getting the group 2 footer. I just create a text box
with a control source of =1 in group 3, and running total set to yes, per
Access help instructions.

However, I've not been able to get a count of group 3 records at the group
1
level. I've tried using =sum([group 2 count]), however, that does not
work.
It seems sum can only be used on database fields, not text box values.
I've
also tried setting up a second text box within group 3, but it only
returns
the same value as in the group 2 footer just before the group 1 break.

I also have a second question. I have a after section break on group 2.
That is OK, except when there is a group 1 break, in which case I do not
want
the group 2 page break. The reason is to get both totals on the same page
on
a group 1 break (assuming I can get that part to work).

 

[Allen Browne]

Regarding my second question, is there a way to suppress my page
break on group level 2 when I have a group level 1 break?

I don't think there's an easy way to do this.

You can place a page break control in the section (instead of setting its
ForceNewPage property), and toggle its Visible property in code. The problem
is in determining when the group 3 record is the last one for the group 1
grouping - particularly if there's a filter or custom sorting applied.