Group Header and Duplicate Fields

[Danu]

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.

 

[Allen Browne]

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.

 

[Danu]

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!

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

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

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.