Count Records in a Group 2nd Request

[Guest]

Any takers on this? I've had no responses...Thanks!

This should be a simple solution but I've tried and tried with no luck and
have searched for help in the newsgroups but I'm not doing something right.
I want to count the number of times a record appears in a group in a report.
The following are my controls in the report.

SalesRep, CustNbr, AmtPd

I have grouped by SalesRep. The CustNbr may appear several times in the
detail section. Even tho the CustNbr appears many times, I only want to count
it once. I have created a second group header for CustNbr and have moved the
control to that header. I have added a text box to the CustNbr header named
CustTotal with the Control Source as =1, Running Sum = Over Group. This gives
me the running count in the report just fine. BUT, I want to use this total
in a calculated control to divide the AmtPaid sum by the total CustNbr.
There are 25 records in the detail section but I have only 24 CustNbr's. I
have created another calculated control in the SalesRep header and I'm trying
to use my new text box "CustTotal" in my calculation. Every time I run the
report, I receive a parameter box with the CustTotal control name in the box.
I've even tried a simple count of CustTotal and I'm still getting the
parameter box.
What am I missing???
Thanks in advance!

 

[Guest]

Ok if I undestand right first lets get the count right
I am going from memory but don't hesitate to post again if it does not work.

Use Sum(FieldYourCounting) id the name property for this field as FieldSumTTL
set it in the right area and you may or maynot even need to set it to count
the group if you place it in the footer of the group.

Once you have the total in FieldSumTTL you do your calculation in a new
field named FieldTotal = ([FieldSumTTL]*2) or whatever.....Now once you have
this calculating right in the proper footer section you set. You can try to
move this field to the header but the results may change if they do just hide
it in the footer section and then add a new field to the header section and
place the = [FieldTotal] in it this will allow it to be placed into the
header if the other way does not....

Please let me know if your successful or need additional help.

If you were a little clearer would have been a little easier....

Dwight Cornett

Also don't forget most calculations in Access I believe start counting with
zero... that could be why your getting 24 instead of 25 if you actually have
25 customer number.

 

[Guest]

I did as you instructed. I added a new text box to the group footer, Control
Source =Sum([Cust_NbrHdr]). Now when I run the report, I am getting a
parameter box for Cust_NbrHdr. Ideas?
Thanks!

Ok if I undestand right first lets get the count right
I am going from memory but don't hesitate to post again if it does not work.

Use Sum(FieldYourCounting) id the name property for this field as FieldSumTTL
set it in the right area and you may or maynot even need to set it to count
the group if you place it in the footer of the group.

Once you have the total in FieldSumTTL you do your calculation in a new
field named FieldTotal = ([FieldSumTTL]*2) or whatever.....Now once you have
this calculating right in the proper footer section you set. You can try to
move this field to the header but the results may change if they do just hide
it in the footer section and then add a new field to the header section and
place the = [FieldTotal] in it this will allow it to be placed into the
header if the other way does not....

Please let me know if your successful or need additional help.

If you were a little clearer would have been a little easier....

Dwight Cornett

Also don't forget most calculations in Access I believe start counting with
zero... that could be why your getting 24 instead of 25 if you actually have
25 customer number.

Any takers on this? I've had no responses...Thanks!

This should be a simple solution but I've tried and tried with no luck and
have searched for help in the newsgroups but I'm not doing something right.
I want to count the number of times a record appears in a group in a report.
The following are my controls in the report.

SalesRep, CustNbr, AmtPd

I have grouped by SalesRep. The CustNbr may appear several times in the
detail section. Even tho the CustNbr appears many times, I only want to count
it once. I have created a second group header for CustNbr and have moved the
control to that header. I have added a text box to the CustNbr header named
CustTotal with the Control Source as =1, Running Sum = Over Group. This gives
me the running count in the report just fine. BUT, I want to use this total
in a calculated control to divide the AmtPaid sum by the total CustNbr.
There are 25 records in the detail section but I have only 24 CustNbr's. I
have created another calculated control in the SalesRep header and I'm trying
to use my new text box "CustTotal" in my calculation. Every time I run the
report, I receive a parameter box with the CustTotal control name in the box.
I've even tried a simple count of CustTotal and I'm still getting the
parameter box.
What am I missing???
Thanks in advance!