Problem with counting unique rows

[Joshann]

I am trying to make a fairly complicated report. I have seen where people have had similar problems, but not exactly the same, so I can't find a solution. Here's the situation:

Our organization is divided into what we call Offices, which are divided into Divisions, which are divided into Teams. I need to report on how many unique cases each Office, Division, and Team worked on. The problem is that a team in Division1 of Office1 may work on the same case as a team in Division2 of Office1. This is what the report should look like (I can't figure out how to use tabs in this forum, so I'm using dashes to indicate where indentions should be):

Note that I am titling the cases "Case Number 1, Case Number 2, etc." Our cases actually have proper names, but I am using "Case Number..." for ease.

Office1
-Division1
---Team1
-----Case Number 1
-----Case Number 2
-----Number of Cases for Team1 = 2
---Team2
-----Case Number 1
-----Number of Cases for Team2 = 1
-Number of Cases for Division1 = 2

-Division 2
---Team1
-----Case Number 3
-----Number of Cases for Team1 = 1
---Team2
-----Case Number 2
-----Case Number 3
-----Number of Cases for Team2 = 2
-Number of Cases for Division2 = 2
Number of Cases for Office1 = 3

Office2
-Division1
---Team1
-----Case Number 1
-----Number of Cases for Team1 = 1
-Number of Cases for Division1 = 1
Number of Cases for Office2 = 1

Overall Total Number of Unique Cases = 3

I have created a report based on a query. Both the query and the report have Office as the outermost group, Division as the next group, and Team as the innermost group. When I try to count the number of cases in my report, I get 3 for Office1, Division1. I get 6 for Office 1, and I get 7 for the overall total. How can I get it to only count unique cases?

Thanks!

 

[Joshann]

Thank you very much. I will try that.