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
|
|||
|
|||
Group Header and Duplicate Fields
I have a report with a group header on the Create Date field of the table. In
the table some records have duplicate order numbers. How would I be able to count unique order numbers by Create Date? Thanks, in advance, for any help. |
#2
|
|||
|
|||
Group Header and Duplicate Fields
In the Sorting and Grouping dialog (View menu in report design view), add
the Order Number field and in the lower pane of the dialog, choose Yes for Group Footer. (You may already have this of course.) Add a text box to the Order Number group footer, and give it these properties: Control Source =1 Running Sum Over Group Name txtOrderCountRS You can set the Visible property of the text box to No, or the Visible property of the section if you prefer. Now in the Create Date footer, add a text box with control source of: =[txtOrderCountRS] Assuming that the OrderNumber is inside the Create Date (i.e. lower down in the Sorting and Grouping dialog), this will give you the count of orders for the date. -- 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. "Danu" wrote in message ... I have a report with a group header on the Create Date field of the table. In the table some records have duplicate order numbers. How would I be able to count unique order numbers by Create Date? Thanks, in advance, for any help. |
#3
|
|||
|
|||
Group Header and Duplicate Fields
Thank you! That did the trick. I had seen an earlier posting using the same
setup and I tried it but must have missed something since it didn't work. Thanks again! "Allen Browne" wrote: In the Sorting and Grouping dialog (View menu in report design view), add the Order Number field and in the lower pane of the dialog, choose Yes for Group Footer. (You may already have this of course.) Add a text box to the Order Number group footer, and give it these properties: Control Source =1 Running Sum Over Group Name txtOrderCountRS You can set the Visible property of the text box to No, or the Visible property of the section if you prefer. Now in the Create Date footer, add a text box with control source of: =[txtOrderCountRS] Assuming that the OrderNumber is inside the Create Date (i.e. lower down in the Sorting and Grouping dialog), this will give you the count of orders for the date. -- 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. "Danu" wrote in message ... I have a report with a group header on the Create Date field of the table. In the table some records have duplicate order numbers. How would I be able to count unique order numbers by Create Date? Thanks, in advance, for any help. |
Thread Tools | |
Display Modes | |
|
|