Solved row count for multiple groups (1 Viewer)

[Jimal]

HI friends
i have prepared my report for display wherein i have made 3groups (groups within group)
now when i use row count , the row counts for the last subgroup.
i would need the row count to display of each of my group.. is there a way to do it.

My report has these groups
Main group: group by destination country
subgroup: group by destination state
subgroup: group by destination town

when i use rowcount inside group: town, its wokring perfectly fine.

I would like to find the row count of the record based on the group state
and further
row count of the record based on the group country

2 main groups : Country A & Country B
Country A has 2 groups state A & State B
state A has two groups town A & town B

I need three counts for the same record based on Country, on state & on town

can anybody throw some light on it pls

 

[theDBguy]

Hi. Have you tried the Running Sum property in your report?

 

[Jimal]

Hi @theDBguy yes i have made running sum over the group - but i couldn't define which group - by default the last group is taken

 

[arnelgp]

you may try using the Detail's Format event to count each detail for that group.
you need to reset the "counter" on page_header format event.

 

[Jimal]

Hi @arnelgp appreciate your replies. but can you brief it up pls. i couldn't understand that in a beginner level

 

[theDBguy]

Hi @theDBguy yes i have made running sum over the group - but i couldn't define which group - by default the last group is taken

Hi. I was talking about the Running Sum "property" on your report. It sounds like you are taking about a running sum in your query.

 

[Jimal]

Hi @theDBguy we are in same understanding only. i used the running sum in the property in the report only

 

[theDBguy]

Hi @theDBguy we are in same understanding only. i used the running sum in the property in the report only
View attachment 94919

Okay, that's good to know. To be able to help you further, you might consider posting a sample db with test data and a copy of your report.

 

[Pat Hartman]

If you have three layers of group, you need three group footers. Each footer contains the count control and it needs to be set to Over Group.

 

[Jimal]

dear @Pat Hartman dear @theDBguy
thanks for your replies..i got your idea but struggling to implement it

I have attached a sample DB - The intent is like this

i need to add a test mesasge after the end of each town (eg. after record of town 'fill' , 'jill','fan' ,'van' etc) and similarly after end of each state 'flip' 'niko' etc.. and similarly after end of each country ..

the message can be as simple as "parcel to XXXX'

how to implement it.. im not geting a idea

 

[arnelgp]

see your report and the code behind the report.

 

[Jimal]

Hi @arnelgp thanks a lot for the idea..
I might ask for too much but your help would mean a lot to me..
i need 3 diffrent row numbers against each record 1) based on country, 2) based on state & 3)based on town.. (of course i was able to get the town row number by using row count) for rest two i failed

 

[arnelgp]

see sample2 report.

 

[Jimal]

Hi @arnelgp you know about lifesaver???? ...thats you sir.., it works
i have used the concept in my Db.. it works ..will connect you if i come acros any cahllenge

 

[Pat Hartman]

If the report ever goes to multiple pages, the counts will be wrong.

Why?

The Format event fires before Access knows whether or not there is room on the page to print the line it just formatted. If there is not room, the Retreat event runs, Access runs the report footer code and than starts a new page and runs all the page and group header code. And finally runs the detail section's on format event again. Usually it just runs twice but there are situations where it could run more than once.

Solution,
There are at least two.
If your formatting (which you would still need) is separate from your counting code, you can bypass the math if the FormatCount property is greater than 1

Another option is to back out the math in the Repeat event.

Bottom line - if you are doing your own math in a report, you need to understand when and why the Format/Retreat events run so you can ensure that you don't double count the last row on each page.