Counting unique values in a report

[Guest]

Hello, I want to have a text box in my report header displaying the number of
customers appearing in my the query result that underlies the report. The
query is called qryOrders and the field I want to count is Customer. I have
for example 20 orders from ten customers. Can you tell me what function I
need to put into the control source of my text box to get the simple result
"10"?

Many thanks in advance

Tim Long

 

[Allen Browne]

1. Add a group header for your CustomerID field (Sorting And Grouping, on
View menu). You can set this section's Visible property to No if you do not
wish to see it.

2. In this section, add a text box with these properties:
Control Source =1
Running Sum Over All
Name txtCustomerCount
Format General Number

3. In the Report Footer section, add a text box with these properties:
Control Source = [txtCustomerCount]
Name txtCustomerCountFooter
Format General Number
Visible No

4. In the Report Header section, you should now be able to add a text box
with Control Source set to:
=[txtCustomerCountFooter]

Without testing, I think that should work. Access will drop the value into
the report footer after the report runs right through, and then carry it
back to the report header section as it makes its 2nd pass on the report.

 

[Guest]

I'm learning! Many thanks for taking the time and trouble to explain.

Tim Long

1. Add a group header for your CustomerID field (Sorting And Grouping, on
View menu). You can set this section's Visible property to No if you do not
wish to see it.

2. In this section, add a text box with these properties:
Control Source =1
Running Sum Over All
Name txtCustomerCount
Format General Number

3. In the Report Footer section, add a text box with these properties:
Control Source = [txtCustomerCount]
Name txtCustomerCountFooter
Format General Number
Visible No

4. In the Report Header section, you should now be able to add a text box
with Control Source set to:
=[txtCustomerCountFooter]

Without testing, I think that should work. Access will drop the value into
the report footer after the report runs right through, and then carry it
back to the report header section as it makes its 2nd pass on the report.

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

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

Hello, I want to have a text box in my report header displaying the number
of
customers appearing in my the query result that underlies the report. The
query is called qryOrders and the field I want to count is Customer. I
have
for example 20 orders from ten customers. Can you tell me what function I
need to put into the control source of my text box to get the simple
result
"10"?

Many thanks in advance

Tim Long

 

[Guest]

I have read this suggestion in a different post as well and i still can't get
it to work.

When I set the control source, for my text box in the reports footer, to
[txtCustomerCount] (or any text box for that matter) i get a "No such Field
in the Field List" error. Please help thank you.

I'm learning! Many thanks for taking the time and trouble to explain.

Tim Long

1. Add a group header for your CustomerID field (Sorting And Grouping, on
View menu). You can set this section's Visible property to No if you do not
wish to see it.

2. In this section, add a text box with these properties:
Control Source =1
Running Sum Over All
Name txtCustomerCount
Format General Number

3. In the Report Footer section, add a text box with these properties:
Control Source = [txtCustomerCount]
Name txtCustomerCountFooter
Format General Number
Visible No

4. In the Report Header section, you should now be able to add a text box
with Control Source set to:
=[txtCustomerCountFooter]

Without testing, I think that should work. Access will drop the value into
the report footer after the report runs right through, and then carry it
back to the report header section as it makes its 2nd pass on the report.

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

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

Hello, I want to have a text box in my report header displaying the number
of
customers appearing in my the query result that underlies the report. The
query is called qryOrders and the field I want to count is Customer. I
have
for example 20 orders from ten customers. Can you tell me what function I
need to put into the control source of my text box to get the simple
result
"10"?

Many thanks in advance

Tim Long

 

[RoyVidar]

Did you just forget the equal sign when referencing the control?
See the detailed info in Allen Browne's #3

=txtCustomerCount

imad hammad wrote in message

I have read this suggestion in a different post as well and i still can't get
it to work.

When I set the control source, for my text box in the reports footer, to
[txtCustomerCount] (or any text box for that matter) i get a "No such Field
in the Field List" error. Please help thank you.

I'm learning! Many thanks for taking the time and trouble to explain.

Tim Long

1. Add a group header for your CustomerID field (Sorting And Grouping, on
View menu). You can set this section's Visible property to No if you do not
wish to see it.

2. In this section, add a text box with these properties:
Control Source =1
Running Sum Over All
Name txtCustomerCount
Format General Number

3. In the Report Footer section, add a text box with these properties:
Control Source = [txtCustomerCount]
Name txtCustomerCountFooter
Format General Number
Visible No

4. In the Report Header section, you should now be able to add a text box
with Control Source set to:
=[txtCustomerCountFooter]

Without testing, I think that should work. Access will drop the value into
the report footer after the report runs right through, and then carry it
back to the report header section as it makes its 2nd pass on the report.

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

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

Hello, I want to have a text box in my report header displaying the number
of
customers appearing in my the query result that underlies the report. The
query is called qryOrders and the field I want to count is Customer. I
have
for example 20 orders from ten customers. Can you tell me what function I
need to put into the control source of my text box to get the simple
result
"10"?

Many thanks in advance

Tim Long

 

[Guest]

I actually forgot the [], but now I am not getting the result i want exactly.

My count in the Report footer is counting correctly (there are a bunch of
spaces between the counts in the report but i plan on setting visiblity to
none). However when i place the text box in the report header (the final
step) the count it displays is always 1.

So in the example Alan wrote

3. In the Report Footer section, add a text box with these properties:
Control Source = [txtCustomerCount]

This step will count properly (up to 5 in my report)

but then the following step will always display a count of 1
4. In the Report Header section, you should now be able to add a text box
with Control Source set to:
=[txtCustomerCountFooter]

Thank you very much for your help

Did you just forget the equal sign when referencing the control?
See the detailed info in Allen Browne's #3

=txtCustomerCount

imad hammad wrote in message

I have read this suggestion in a different post as well and i still can't get
it to work.

When I set the control source, for my text box in the reports footer, to
[txtCustomerCount] (or any text box for that matter) i get a "No such Field
in the Field List" error. Please help thank you.

I'm learning! Many thanks for taking the time and trouble to explain.

Tim Long

:

1. Add a group header for your CustomerID field (Sorting And Grouping, on
View menu). You can set this section's Visible property to No if you do not
wish to see it.

2. In this section, add a text box with these properties:
Control Source =1
Running Sum Over All
Name txtCustomerCount
Format General Number

3. In the Report Footer section, add a text box with these properties:
Control Source = [txtCustomerCount]
Name txtCustomerCountFooter
Format General Number
Visible No

4. In the Report Header section, you should now be able to add a text box
with Control Source set to:
=[txtCustomerCountFooter]

Without testing, I think that should work. Access will drop the value into
the report footer after the report runs right through, and then carry it
back to the report header section as it makes its 2nd pass on the report.

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

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

Hello, I want to have a text box in my report header displaying the number
of
customers appearing in my the query result that underlies the report. The
query is called qryOrders and the field I want to count is Customer. I
have
for example 20 orders from ten customers. Can you tell me what function I
need to put into the control source of my text box to get the simple
result
"10"?

Many thanks in advance

Tim Long

 

[RoyVidar]

Sorry, I don't know - Allen Browne's suggestion works
on my setup - you are sure you are referencing the
correct control?

imad hammad wrote in message

I actually forgot the [], but now I am not getting the result i want exactly.

My count in the Report footer is counting correctly (there are a bunch of
spaces between the counts in the report but i plan on setting visiblity to
none). However when i place the text box in the report header (the final
step) the count it displays is always 1.

So in the example Alan wrote

3. In the Report Footer section, add a text box with these properties:
Control Source = [txtCustomerCount]

This step will count properly (up to 5 in my report)

but then the following step will always display a count of 1
4. In the Report Header section, you should now be able to add a text box
with Control Source set to:
=[txtCustomerCountFooter]

Thank you very much for your help

Did you just forget the equal sign when referencing the control?
See the detailed info in Allen Browne's #3

=txtCustomerCount

imad hammad wrote in message

I have read this suggestion in a different post as well and i still can't
get it to work.

When I set the control source, for my text box in the reports footer, to
[txtCustomerCount] (or any text box for that matter) i get a "No such Field
in the Field List" error. Please help thank you.

:

I'm learning! Many thanks for taking the time and trouble to explain.

Tim Long

:

1. Add a group header for your CustomerID field (Sorting And Grouping, on
View menu). You can set this section's Visible property to No if you do
not wish to see it.

2. In this section, add a text box with these properties:
Control Source =1
Running Sum Over All
Name txtCustomerCount
Format General Number

3. In the Report Footer section, add a text box with these properties:
Control Source = [txtCustomerCount]
Name txtCustomerCountFooter
Format General Number
Visible No

4. In the Report Header section, you should now be able to add a text box
with Control Source set to:
=[txtCustomerCountFooter]

Without testing, I think that should work. Access will drop the value
into the report footer after the report runs right through, and then
carry it back to the report header section as it makes its 2nd pass on
the report.

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

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

Hello, I want to have a text box in my report header displaying the
number of
customers appearing in my the query result that underlies the report.
The query is called qryOrders and the field I want to count is Customer.
I have
for example 20 orders from ten customers. Can you tell me what function
I need to put into the control source of my text box to get the simple
result
"10"?

Many thanks in advance

Tim Long

 

[Guest]

Allen,

Thank you for the insight on this. I was able to use your idea and mixed it
with some info from a Duane Hookom in another thread and was able to get the
counts I was looking for. The only question I have is, when I run the
report, there is a chance I'll get 0 records from my queries. In this case,
I get #Error in the text boxes where my counts should show zeros. Is there a
way to have it show zeros instead of #Error
--
--
Steve Steiner
(e-mail address removed)

1. Add a group header for your CustomerID field (Sorting And Grouping, on
View menu). You can set this section's Visible property to No if you do not
wish to see it.

2. In this section, add a text box with these properties:
Control Source =1
Running Sum Over All
Name txtCustomerCount
Format General Number

3. In the Report Footer section, add a text box with these properties:
Control Source = [txtCustomerCount]
Name txtCustomerCountFooter
Format General Number
Visible No

4. In the Report Header section, you should now be able to add a text box
with Control Source set to:
=[txtCustomerCountFooter]

Without testing, I think that should work. Access will drop the value into
the report footer after the report runs right through, and then carry it
back to the report header section as it makes its 2nd pass on the report.

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

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

Hello, I want to have a text box in my report header displaying the number
of
customers appearing in my the query result that underlies the report. The
query is called qryOrders and the field I want to count is Customer. I
have
for example 20 orders from ten customers. Can you tell me what function I
need to put into the control source of my text box to get the simple
result
"10"?

Many thanks in advance

Tim Long

 

[Marshall Barton]

Thank you for the insight on this. I was able to use your idea and mixed it
with some info from a Duane Hookom in another thread and was able to get the
counts I was looking for. The only question I have is, when I run the
report, there is a chance I'll get 0 records from my queries. In this case,
I get #Error in the text boxes where my counts should show zeros. Is there a
way to have it show zeros instead of #Error

Instead of using:
=txtCustomerCount

try using an expression like:
=IIf(IsError(txtCustomerCount), 0, txtCustomerCount)

 

[Guest]

Thank you Marshall!


Your suggestion worked like a charm!

The only modification I needed to make was to add tht [] around the control
source.

Thank you again.