DisctinctCount grand total value incorrect
I have been tasked with converting a case volume report generated in Access then manually manipulated in Excel into an automated crystal report. The goal is the obtain for a one month period the number of cases performed in a given OR suite. It need to be grouped by facility, room_name, admission type (separated into to inpatient or outpatient) and a grand total of all cases.
I have sorted the data as required and I have distinct count summaries of the case_number field providing accurate numbers for both the room_name and the admission type sort. The issue occurs when I apply the distinct count of case_number as a sub total for either the facility level or grand total. In both rolled up total scenarios the distinct count is a slightly less than what it should be.
I have verified that the summary fields are working properly by cross checking with running totals, distinct count formulas and a hand count of the records. I should be seeing 85 cases for facility A and 531 cases for facility B for a grand total of 616 cases.
Instead what I see is 84 cases for facility A, 523 cases for facility B and 607 cases for the grand total. While the GT does agree with the facility counts, the facility total counts are incorrect. A distinct count running total also yields the same erred results as does a distinct count formula. Any thoughts would be greatly appreciated.