esu4236
asked on
Hide duplicates and only count those records
I'm helping a user set up a report where he wants to hide the duplicates. That's no problem. The problem is he wants to count the records under each category, but he only wants to count those records that are unique. So in other words, if the Office Name "Taylor Service Center" appeared 3 times under a category, he only wants to display it once and count it once. How do I count only unique records??? We're using Access 97. If I have to use code, please be very specific, as I'm not a programmer. Thanks.
ASKER
The report is already grouped on the Office Name. I have a group footer set up and that's where I'm doing the count function. I cannot do a RunningSum or a Sum function on this field, as it's not a numerical field. It's text. So I think I have to do some sort of counting, but I need to only count unique records.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can also do the count right in the report. In the report declarations section, dim a variable to hold the total:
Dim intItemCount as integer
then in the Group Headers OnPrint event do:
intItemCount = 0
then in the detail sections OnFormat event do:
If FormatCount = 1 then
intItemCount = intItemCount + 1
End If
and last, in the Group Footers OnFormat event, place the count in a unbound text control:
Me![txtGroupCount] = intItemCount
'txtGroupCount' is the name of the control that you create.
Jim.
Dim intItemCount as integer
then in the Group Headers OnPrint event do:
intItemCount = 0
then in the detail sections OnFormat event do:
If FormatCount = 1 then
intItemCount = intItemCount + 1
End If
and last, in the Group Footers OnFormat event, place the count in a unbound text control:
Me![txtGroupCount] = intItemCount
'txtGroupCount' is the name of the control that you create.
Jim.
ASKER
I ended up adding another column in the underlying query where it is counting the office names. And then in the report, I'm counting the total office names under each agency in a group footer.
JDettman - I wanted to try your suggestion, but I didn't understand it. I didn't understand what you meant about "in the report declarations section, dim a variable". I had no idea where to go for this. Sorry.
JDettman - I wanted to try your suggestion, but I didn't understand it. I didn't understand what you meant about "in the report declarations section, dim a variable". I had no idea where to go for this. Sorry.
-Chuck