counting unique values in report footers

[Sarah]

I am trying to count the number of unique account numbers in my report by
branch, region, and report. At first all I needed was a branch sum, so I
gave the Account Number field a header row, added a text box in it with the
control source value of 1, selected to have a running sum over the group, and
entered a field in the branch number footer that equals
[txtAccountNumberCount]. This worked perfectly to count the unique account
numbers for each branch.

Now, I need to add a Region footer that counts the unique branch numbers per
region. I also need to add a Report footer that counts the unique branch
numbers over the entire report. When I try to use the technique above to do
this (by copying and pasting the text box from the branch footer into the
region or report footer), I either get the result of "1", the total number of
accounts (not unique), or a blank. I've tried changing the running sum from
"Over Group" to "Over All" and still have not produced the desired results.
I tried creating a new txt field to use for each footer and that did not
produce the correct results either.

Is there a simple solution I am missing? I can't figure out how to make
this work.

Thanks!

 

[Marshall Barton]

I am trying to count the number of unique account numbers in my report by
branch, region, and report. At first all I needed was a branch sum, so I
gave the Account Number field a header row, added a text box in it with the
control source value of 1, selected to have a running sum over the group, and
entered a field in the branch number footer that equals
[txtAccountNumberCount]. This worked perfectly to count the unique account
numbers for each branch.

Now, I need to add a Region footer that counts the unique branch numbers per
region. I also need to add a Report footer that counts the unique branch
numbers over the entire report. When I try to use the technique above to do
this (by copying and pasting the text box from the branch footer into the
region or report footer), I either get the result of "1", the total number of
accounts (not unique), or a blank. I've tried changing the running sum from
"Over Group" to "Over All" and still have not produced the desired results.
I tried creating a new txt field to use for each footer and that did not
produce the correct results either.

Not sure I understand what the issue is, but here's a
general outline that may help.

To count the number of branches in a region, add a running
sum Over Group text box to the branch group header or footer
section then refer to it in the region footer section.

To get a total of the branches in the entire report, add a
(different) running sum Over All text box to the branch
header or footer section and refer to it in the report
footer.

 

[Sarah]

The problem is that I need to count only the unique values. I am able to do
this through the process I outlined in my first paragraph to get the data
into the branch footer, but when I try to do the same thing and get a count
of unique account numbers into the region or report footer, the count does
not work correctly. I would like to be able to get a count of all distinct
account numbers within each region and within the entire report, just as I
did for the branches.

I am trying to count the number of unique account numbers in my report by
branch, region, and report. At first all I needed was a branch sum, so I
gave the Account Number field a header row, added a text box in it with the
control source value of 1, selected to have a running sum over the group, and
entered a field in the branch number footer that equals
[txtAccountNumberCount]. This worked perfectly to count the unique account
numbers for each branch.

Now, I need to add a Region footer that counts the unique branch numbers per
region. I also need to add a Report footer that counts the unique branch
numbers over the entire report. When I try to use the technique above to do
this (by copying and pasting the text box from the branch footer into the
region or report footer), I either get the result of "1", the total number of
accounts (not unique), or a blank. I've tried changing the running sum from
"Over Group" to "Over All" and still have not produced the desired results.
I tried creating a new txt field to use for each footer and that did not
produce the correct results either.

Not sure I understand what the issue is, but here's a
general outline that may help.

To count the number of branches in a region, add a running
sum Over Group text box to the branch group header or footer
section then refer to it in the region footer section.

To get a total of the branches in the entire report, add a
(different) running sum Over All text box to the branch
header or footer section and refer to it in the report
footer.

 

[Marshall Barton]

OK, then add another textbox named txtRunBranchNumberCount
to the Branch group footer section. Set its expression to
=txtAccountNumberCount (just like the one that displays the
branch count) but set this one's Running Sum to Over Group.
Then the Region footer can display its total by using a text
box with the expression =txtRunBranchNumberCount

You can use this same approach in the region footer to
accumulate a total count for any higher group (Over Group)
or the report (Over All)
--
Marsh
MVP [MS Access]

The problem is that I need to count only the unique values. I am able to do
this through the process I outlined in my first paragraph to get the data
into the branch footer, but when I try to do the same thing and get a count
of unique account numbers into the region or report footer, the count does
not work correctly. I would like to be able to get a count of all distinct
account numbers within each region and within the entire report, just as I
did for the branches.

I am trying to count the number of unique account numbers in my report by
branch, region, and report. At first all I needed was a branch sum, so I
gave the Account Number field a header row, added a text box in it with the
control source value of 1, selected to have a running sum over the group, and
entered a field in the branch number footer that equals
[txtAccountNumberCount]. This worked perfectly to count the unique account
numbers for each branch.

Now, I need to add a Region footer that counts the unique branch numbers per
region. I also need to add a Report footer that counts the unique branch
numbers over the entire report. When I try to use the technique above to do
this (by copying and pasting the text box from the branch footer into the
region or report footer), I either get the result of "1", the total number of
accounts (not unique), or a blank. I've tried changing the running sum from
"Over Group" to "Over All" and still have not produced the desired results.
I tried creating a new txt field to use for each footer and that did not
produce the correct results either.

Not sure I understand what the issue is, but here's a
general outline that may help.

To count the number of branches in a region, add a running
sum Over Group text box to the branch group header or footer
section then refer to it in the region footer section.

To get a total of the branches in the entire report, add a
(different) running sum Over All text box to the branch
header or footer section and refer to it in the report
footer.

 

[Sarah]

Thank you! Thank you! Thank you! This saved my life.

The only thing that I may have done diffently than you suggested (or maybe I
just interpreted your suggestion incorrectly) was that to get a report total,
I had to add a hidden textbox to the branch footer again, instead of to the
region footer. I set the additional branch footer to "over all" as you
suggested, added the new field name to the report footer, and everything
calculated correctly. Thank you again!

OK, then add another textbox named txtRunBranchNumberCount
to the Branch group footer section. Set its expression to
=txtAccountNumberCount (just like the one that displays the
branch count) but set this one's Running Sum to Over Group.
Then the Region footer can display its total by using a text
box with the expression =txtRunBranchNumberCount

You can use this same approach in the region footer to
accumulate a total count for any higher group (Over Group)
or the report (Over All)
--
Marsh
MVP [MS Access]

The problem is that I need to count only the unique values. I am able to do
this through the process I outlined in my first paragraph to get the data
into the branch footer, but when I try to do the same thing and get a count
of unique account numbers into the region or report footer, the count does
not work correctly. I would like to be able to get a count of all distinct
account numbers within each region and within the entire report, just as I
did for the branches.

Sarah wrote:
I am trying to count the number of unique account numbers in my report by
branch, region, and report. At first all I needed was a branch sum, so I
gave the Account Number field a header row, added a text box in it with the
control source value of 1, selected to have a running sum over the group, and
entered a field in the branch number footer that equals
[txtAccountNumberCount]. This worked perfectly to count the unique account
numbers for each branch.

Now, I need to add a Region footer that counts the unique branch numbers per
region. I also need to add a Report footer that counts the unique branch
numbers over the entire report. When I try to use the technique above to do
this (by copying and pasting the text box from the branch footer into the
region or report footer), I either get the result of "1", the total number of
accounts (not unique), or a blank. I've tried changing the running sum from
"Over Group" to "Over All" and still have not produced the desired results.
I tried creating a new txt field to use for each footer and that did not
produce the correct results either.

Not sure I understand what the issue is, but here's a
general outline that may help.

To count the number of branches in a region, add a running
sum Over Group text box to the branch group header or footer
section then refer to it in the region footer section.

To get a total of the branches in the entire report, add a
(different) running sum Over All text box to the branch
header or footer section and refer to it in the report
footer.

 

[Marshall Barton]

You can do it either way. I suggesting adding the region
totals and you're adding the Branch totals. Both will sum to
the same Over All total so don't worry about which way you
do it.
--
Marsh
MVP [MS Access]

Thank you! Thank you! Thank you! This saved my life.

The only thing that I may have done diffently than you suggested (or maybe I
just interpreted your suggestion incorrectly) was that to get a report total,
I had to add a hidden textbox to the branch footer again, instead of to the
region footer. I set the additional branch footer to "over all" as you
suggested, added the new field name to the report footer, and everything
calculated correctly. Thank you again!

OK, then add another textbox named txtRunBranchNumberCount
to the Branch group footer section. Set its expression to
=txtAccountNumberCount (just like the one that displays the
branch count) but set this one's Running Sum to Over Group.
Then the Region footer can display its total by using a text
box with the expression =txtRunBranchNumberCount

You can use this same approach in the region footer to
accumulate a total count for any higher group (Over Group)
or the report (Over All)

The problem is that I need to count only the unique values. I am able to do
this through the process I outlined in my first paragraph to get the data
into the branch footer, but when I try to do the same thing and get a count
of unique account numbers into the region or report footer, the count does
not work correctly. I would like to be able to get a count of all distinct
account numbers within each region and within the entire report, just as I
did for the branches.


Sarah wrote:
I am trying to count the number of unique account numbers in my report by
branch, region, and report. At first all I needed was a branch sum, so I
gave the Account Number field a header row, added a text box in it with the
control source value of 1, selected to have a running sum over the group, and
entered a field in the branch number footer that equals
[txtAccountNumberCount]. This worked perfectly to count the unique account
numbers for each branch.

Now, I need to add a Region footer that counts the unique branch numbers per
region. I also need to add a Report footer that counts the unique branch
numbers over the entire report. When I try to use the technique above to do
this (by copying and pasting the text box from the branch footer into the
region or report footer), I either get the result of "1", the total number of
accounts (not unique), or a blank. I've tried changing the running sum from
"Over Group" to "Over All" and still have not produced the desired results.
I tried creating a new txt field to use for each footer and that did not
produce the correct results either.


:
Not sure I understand what the issue is, but here's a
general outline that may help.

To count the number of branches in a region, add a running
sum Over Group text box to the branch group header or footer
section then refer to it in the region footer section.

To get a total of the branches in the entire report, add a
(different) running sum Over All text box to the branch
header or footer section and refer to it in the report
footer.

 

[Sarah]

I am baffled by what Access is doing in my report now. This is a different
report than the one referenced earlier in this post, but it is very similar
and is based on the same table of data - the one I was working on before is a
detail report, and this one is a summary. I have set up hidden fields that
total unique account numbers by branch, region, and total bank.

My report is set up to break by regions. Some regions carry over to a
second page. When this happens, for some reason that I can't fathom, the
first branch on the second page shows an additional account number in its
total. If branch 10 is the first branch on the 2nd page of the eastern
region and it contains 9 unique accounts, the report shows 10 accounts. I
don't know why this is happening, but it is consistent through all regions
and the offage adjusts to the new branch if I resize the detail row to move a
new branch to the top of the second page.

Can you think of what might be causing this?

Thanks!

Sarah

You can do it either way. I suggesting adding the region
totals and you're adding the Branch totals. Both will sum to
the same Over All total so don't worry about which way you
do it.
--
Marsh
MVP [MS Access]

Thank you! Thank you! Thank you! This saved my life.

The only thing that I may have done diffently than you suggested (or maybe I
just interpreted your suggestion incorrectly) was that to get a report total,
I had to add a hidden textbox to the branch footer again, instead of to the
region footer. I set the additional branch footer to "over all" as you
suggested, added the new field name to the report footer, and everything
calculated correctly. Thank you again!

OK, then add another textbox named txtRunBranchNumberCount
to the Branch group footer section. Set its expression to
=txtAccountNumberCount (just like the one that displays the
branch count) but set this one's Running Sum to Over Group.
Then the Region footer can display its total by using a text
box with the expression =txtRunBranchNumberCount

You can use this same approach in the region footer to
accumulate a total count for any higher group (Over Group)
or the report (Over All)


Sarah wrote:
The problem is that I need to count only the unique values. I am able to do
this through the process I outlined in my first paragraph to get the data
into the branch footer, but when I try to do the same thing and get a count
of unique account numbers into the region or report footer, the count does
not work correctly. I would like to be able to get a count of all distinct
account numbers within each region and within the entire report, just as I
did for the branches.


Sarah wrote:
I am trying to count the number of unique account numbers in my report by
branch, region, and report. At first all I needed was a branch sum, so I
gave the Account Number field a header row, added a text box in it with the
control source value of 1, selected to have a running sum over the group, and
entered a field in the branch number footer that equals
[txtAccountNumberCount]. This worked perfectly to count the unique account
numbers for each branch.

Now, I need to add a Region footer that counts the unique branch numbers per
region. I also need to add a Report footer that counts the unique branch
numbers over the entire report. When I try to use the technique above to do
this (by copying and pasting the text box from the branch footer into the
region or report footer), I either get the result of "1", the total number of
accounts (not unique), or a blank. I've tried changing the running sum from
"Over Group" to "Over All" and still have not produced the desired results.
I tried creating a new txt field to use for each footer and that did not
produce the correct results either.


:
Not sure I understand what the issue is, but here's a
general outline that may help.

To count the number of branches in a region, add a running
sum Over Group text box to the branch group header or footer
section then refer to it in the region footer section.

To get a total of the branches in the entire report, add a
(different) running sum Over All text box to the branch
header or footer section and refer to it in the report
footer.

 

[Marshall Barton]

I am baffled by what Access is doing in my report now. This is a different
report than the one referenced earlier in this post, but it is very similar
and is based on the same table of data - the one I was working on before is a
detail report, and this one is a summary. I have set up hidden fields that
total unique account numbers by branch, region, and total bank.

My report is set up to break by regions. Some regions carry over to a
second page. When this happens, for some reason that I can't fathom, the
first branch on the second page shows an additional account number in its
total. If branch 10 is the first branch on the 2nd page of the eastern
region and it contains 9 unique accounts, the report shows 10 accounts. I
don't know why this is happening, but it is consistent through all regions
and the offage adjusts to the new branch if I resize the detail row to move a
new branch to the top of the second page.

That sounds very strange and I can not think of anything
that could cause it except some very unusual code in an
event procedure.

Is there anything about the extra record that might provide
some clues?