I have a report that sums projected sales revenue by consultant by client name. For example, John Smith who made two sales to ABC Corp with projected revenue of $1000 and $5000, I want to report:
John Smith $6000 1
where 1 is the number of distinct clients names.
Currently my report is counting 2, which is the number of records.
Question: can this only be done by adding a query to summarize the data before it gets to the report?
Have the report Group by Consultant, then by client. Put a hidden text box with a recordsource =1, running sum over group in the header of the client group, and your sum in the footer og the consultant group.
In the consultant footer, the hidden text box in the client header will contain the number of clients (it will reset for each group).
Edit: og .... time to start drinking! Obviously I meant of ...