Running Sum Text box in report not accumulating sums

[P. Scaram]

I use access2000

I have read the Kb articles 129096 and 115877 about calculating sums over
groups in reports in Access.
However for my report it does not work , there must be something else wrong.

The group field is a check box is that perhaps a problem.?

I do not get a correct sum even for the simple case

Name: testcount
ControlSource : = 1

In the group footer

Name : text15
ControlSource Val([testcount])
RunningSum: Over group.

This is a simple case. I get the same problem on the following cases too.

Name counter1
ControlSource =IIf([Reports]![ProsfStatistics].[DiffCallEAP]<>"X";1;0)

In the group footer

Name : text16
ControlSource = Val([counter1])
RunningSum: Over group.

It still does not work in this case.
I am programming with access for years.
When I use a simple report created with the wizard the running sum works.
There must be something fundamentally wrong. Any suggestions.?

Thanks in advance

P.Scaram

 

[Allen Browne]

Suggestions:

testcount:
=======
- How is the Running Sum property set?
- Set its Format property to General Number (or similar) so Access knows the
data type.
- Temporarily set its Visible property to Yes so you can see it accumulating
the values.

text15:
=====
- ControlSource must start with =.
- Val() is not needed.
- Again, set the Format property to a numeric format.
- If you have only set the Running Sum here (not for testcount above), it
would only collect 1 for each group, so you could just use a ControlSource
of:
=1

text16:
=====
I doubt this will work for an expression.
It cannot work for another report.
Try a ControlSource of:
=Sum(IIf([DiffCallEAP]<>"X", 1, 0))

 

[P. Scaram]

Thank very much for the prompt answer.

I tried what you suggested setting format numeric and also setting running
sum to "Over Group" for the field in the detail section as well as the field
in the group footer section. I had not thought of that, thanks. It worked.By
the way for your information it also worked for the field with the " IIf
(...)" expresion as well.

One remaining problem. the grouping field as I said is a Yes/No check box.
The running sums for the second grouping also include the totals from the
first grouping , like having declared "Over all" . ie I get a correct sum for
the YES grouping but for the NO grouping I get a total sum (YES and NO)not
only the sum expected for the "NO grouping ".This still presents a problem.

Any suggestions please.

P. Scaram

Suggestions:

testcount:
=======
- How is the Running Sum property set?
- Set its Format property to General Number (or similar) so Access knows the
data type.
- Temporarily set its Visible property to Yes so you can see it accumulating
the values.

text15:
=====
- ControlSource must start with =.
- Val() is not needed.
- Again, set the Format property to a numeric format.
- If you have only set the Running Sum here (not for testcount above), it
would only collect 1 for each group, so you could just use a ControlSource
of:
=1

text16:
=====
I doubt this will work for an expression.
It cannot work for another report.
Try a ControlSource of:
=Sum(IIf([DiffCallEAP]<>"X", 1, 0))

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

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

I use access2000

I have read the Kb articles 129096 and 115877 about calculating sums over
groups in reports in Access.
However for my report it does not work , there must be something else
wrong.

The group field is a check box is that perhaps a problem.?

I do not get a correct sum even for the simple case

Name: testcount
ControlSource : = 1

In the group footer

Name : text15
ControlSource Val([testcount])
RunningSum: Over group.

This is a simple case. I get the same problem on the following cases too.

Name counter1
ControlSource =IIf([Reports]![ProsfStatistics].[DiffCallEAP]<>"X";1;0)

In the group footer

Name : text16
ControlSource = Val([counter1])
RunningSum: Over group.

It still does not work in this case.
I am programming with access for years.
When I use a simple report created with the wizard the running sum works.
There must be something fundamentally wrong. Any suggestions.?

Thanks in advance

P.Scaram

 

[Allen Browne]

Access uses -1 for True, and 0 for False.

Therefore if you SUM the field, you get the count of yesses.

Count of Yes in a field named YN:
= - Sum([YN])

Total count (regardless of yes or no):
= Count([YN])

Therefore count of No:
=Count([YN]) + Sum([YN])

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

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

Thank very much for the prompt answer.

I tried what you suggested setting format numeric and also setting running
sum to "Over Group" for the field in the detail section as well as the
field
in the group footer section. I had not thought of that, thanks. It
worked.By
the way for your information it also worked for the field with the " IIf
(...)" expresion as well.

One remaining problem. the grouping field as I said is a Yes/No check box.
The running sums for the second grouping also include the totals from the
first grouping , like having declared "Over all" . ie I get a correct sum
for
the YES grouping but for the NO grouping I get a total sum (YES and NO)not
only the sum expected for the "NO grouping ".This still presents a
problem.

Any suggestions please.

P. Scaram

Suggestions:

testcount:
=======
- How is the Running Sum property set?
- Set its Format property to General Number (or similar) so Access knows
the
data type.
- Temporarily set its Visible property to Yes so you can see it
accumulating
the values.

text15:
=====
- ControlSource must start with =.
- Val() is not needed.
- Again, set the Format property to a numeric format.
- If you have only set the Running Sum here (not for testcount above), it
would only collect 1 for each group, so you could just use a
ControlSource
of:
=1

text16:
=====
I doubt this will work for an expression.
It cannot work for another report.
Try a ControlSource of:
=Sum(IIf([DiffCallEAP]<>"X", 1, 0))

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

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

I use access2000

I have read the Kb articles 129096 and 115877 about calculating sums
over
groups in reports in Access.
However for my report it does not work , there must be something else
wrong.

The group field is a check box is that perhaps a problem.?

I do not get a correct sum even for the simple case

Name: testcount
ControlSource : = 1

In the group footer

Name : text15
ControlSource Val([testcount])
RunningSum: Over group.

This is a simple case. I get the same problem on the following cases
too.

Name counter1
ControlSource =IIf([Reports]![ProsfStatistics].[DiffCallEAP]<>"X";1;0)

In the group footer

Name : text16
ControlSource = Val([counter1])
RunningSum: Over group.

It still does not work in this case.
I am programming with access for years.
When I use a simple report created with the wizard the running sum
works.
There must be something fundamentally wrong. Any suggestions.?

Thanks in advance

P.Scaram

 

[John Spencer]

It appears to me that you are setting the Running Sum property on the
wrong control.

If you want to count the number of detail lines you should have the
control in the detail section
Name: Text18
Control Source: =1
Running Sum: Over Group
(That will increment one time for each record in the detail, until the
group changes.)

You can refer to text 18 in another control in the group footer.
Name: Text20
Control Source: =[Text18]

Of course there are other ways to get a count of the records in the
detail group.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thank very much for the prompt answer.

I tried what you suggested setting format numeric and also setting running
sum to "Over Group" for the field in the detail section as well as the field
in the group footer section. I had not thought of that, thanks. It worked.By
the way for your information it also worked for the field with the " IIf
(...)" expresion as well.

One remaining problem. the grouping field as I said is a Yes/No check box.
The running sums for the second grouping also include the totals from the
first grouping , like having declared "Over all" . ie I get a correct sum for
the YES grouping but for the NO grouping I get a total sum (YES and NO)not
only the sum expected for the "NO grouping ".This still presents a problem.

Any suggestions please.

P. Scaram

Suggestions:

testcount:
=======
- How is the Running Sum property set?
- Set its Format property to General Number (or similar) so Access knows the
data type.
- Temporarily set its Visible property to Yes so you can see it accumulating
the values.

text15:
=====
- ControlSource must start with =.
- Val() is not needed.
- Again, set the Format property to a numeric format.
- If you have only set the Running Sum here (not for testcount above), it
would only collect 1 for each group, so you could just use a ControlSource
of:
=1

text16:
=====
I doubt this will work for an expression.
It cannot work for another report.
Try a ControlSource of:
=Sum(IIf([DiffCallEAP]<>"X", 1, 0))

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

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

I use access2000

I have read the Kb articles 129096 and 115877 about calculating sums over
groups in reports in Access.
However for my report it does not work , there must be something else
wrong.

The group field is a check box is that perhaps a problem.?

I do not get a correct sum even for the simple case

Name: testcount
ControlSource : = 1

In the group footer

Name : text15
ControlSource Val([testcount])
RunningSum: Over group.

This is a simple case. I get the same problem on the following cases too.

Name counter1
ControlSource =IIf([Reports]![ProsfStatistics].[DiffCallEAP]<>"X";1;0)

In the group footer

Name : text16
ControlSource = Val([counter1])
RunningSum: Over group.

It still does not work in this case.
I am programming with access for years.
When I use a simple report created with the wizard the running sum works.
There must be something fundamentally wrong. Any suggestions.?

Thanks in advance

P.Scaram

 

[P. Scaram]

I got the complementary sum for the group by putting the sum in the footer of
the report using grouping "over all".
Thanks for your help