Count records in a group

[rjmccafferty]

In an Access report you can count records by group. The field that I use to keep the running count per record works fine and resets itself when each new group starts. In my specific application I actually have a group within a group and count the records in the second tier group, which works fine. For the detail field for the running count, in properties, I fill in the Name field, set controlSource to =1, RunningSum to OverGroup and visible to No (although for testing I leave it visible).

In the group footer, I put another field with recordsource as =RecordCount (which is the name of the above field that keeps the running count). Instead of giving me the total for each individual group, it gives an increasing number that is the total for all the groups to that point. So, if each of the first 4 groups had 5 records each (for simplicity of example), within the groups we would see the number in each one increase from 1 to 5, record by record in the detail section, starting over at one in each new group. In the group footer, however, after the first group we would see 5. After the second group it would be 10, after the third group it would be 15, etc.

The methods I used, as described above, are exactly what the Access 2000 help menu says to do. What do I need to do differently to get the group totals to be accurate?

 

[sko]

That's how a running sum is supposed to work. If all you want is the total records in each individual group add a text box to the group footer. In the control source enter
=Count(*)

HTH

 

[jen89]

Make sure your running sum is set to "over group" not "over all"

 

[3734kumo]

If I wanted a count of records based upon a value in an other field, would the statement in the text box look like:

=if xfield = 1 then count(*)

Also, I see both IF and IIF used: what is the difference?

Thanks for any help

 

[strikermax]

I have had the same problem, and what sko said was true, but you need to set the running sum to "No." So the following settings would apply:

ControlSource: =Count(*)
InputMask:
RunningSum: No

If your "total" field is in the group footer, you should see a total for each group and not encounter the problem which rjmccafferty ran into.

"Three o'clock is always too late or too early for anything you want to do."