If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Counting unique values in a report
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 |
#2
|
|||
|
|||
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. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Tim Long" wrote in message ... 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 |
#3
|
|||
|
|||
I'm learning! Many thanks for taking the time and trouble to explain.
Tim Long "Allen Browne" wrote: 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. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Tim Long" wrote in message ... 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 |
#4
|
|||
|
|||
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. "Tim Long" wrote: I'm learning! Many thanks for taking the time and trouble to explain. Tim Long "Allen Browne" wrote: 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. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Tim Long" wrote in message ... 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 |
#5
|
|||
|
|||
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. "Tim Long" wrote: I'm learning! Many thanks for taking the time and trouble to explain. Tim Long "Allen Browne" wrote: 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. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Tim Long" wrote in message ... 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 -- Roy-Vidar |
#6
|
|||
|
|||
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 "RoyVidar" wrote: 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. "Tim Long" wrote: I'm learning! Many thanks for taking the time and trouble to explain. Tim Long "Allen Browne" wrote: 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. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Tim Long" wrote in message ... 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 -- Roy-Vidar |
#7
|
|||
|
|||
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 "RoyVidar" wrote: 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. "Tim Long" wrote: I'm learning! Many thanks for taking the time and trouble to explain. Tim Long "Allen Browne" wrote: 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. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Tim Long" wrote in message ... 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 -- Roy-Vidar -- Roy-Vidar |
#9
|
|||
|
|||
Counting unique values in a report
seswho704 wrote:
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) -- Marsh MVP [MS Access] |
#10
|
|||
|
|||
Counting unique values in a report
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. -- -- Steve Steiner "Marshall Barton" wrote: seswho704 wrote: 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) -- Marsh MVP [MS Access] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Unique Values vs Unique Records | Miaplacidus | Running & Setting Up Queries | 1 | September 17th, 2004 08:24 PM |
Counting Records in a Access Report | Armando B. | General Discussion | 1 | August 10th, 2004 06:32 PM |
Save Report With CreateReport Coding Issue | Jeff Conrad | Setting Up & Running Reports | 8 | July 12th, 2004 08:39 AM |
Counting Unique Values | Emma Hope | Worksheet Functions | 4 | May 9th, 2004 11:40 PM |