Unanswered: Total in Report Summary Needs to be Distinct Count
My Access report is filtered based on dynamically built report criteria. When items are selected from the form's list box, a criteria statement is built, for example:
SELECT [Client System] IN ("AS400","AS 400",...etc.
This string is placed in the variable criteria, then the variable is used with the statement that opens the report:
DoCmd.OpenReport "reportName", acViewPreview, acSaveNo, Criteria
When I produce a summary report of two fields, [Client Name] and [Client System], there can be 5 unique rows correctly displayed on the report. On the source table, a client may be called more than one time, so there is a record for each call and there is duplication because some clients have been called more than once.
The problem: how do I get the distinct count of client names to appear at the bottom of the report. In the example, where one client was called twice, the count displayed is 6 when it should be 5.
I have created a string of SQL text to get the count I need, and if this is the solution, where do I go from there to get the total into the report?
No, the reports are short. The multi-select list is being added so that several items can be selected because of spelling variation. It would be unreasonable to produce a report with one line to a page. Not sure how this would correct my total to display the count of distinct client names.
The report is grouped and shows no duplicates. Its the total at the end that doesn't work because it counts the duplicates.
I created a stored query with the two fields grouped, instead of using the table, which has duplicates, as the source for the report data. Counting records from this query gives me a distinct count in the report summary.