group/subgroup counts problem

[The_Doc_Man]

Just switched to Access 2007 and I'm having trouble with a group aggregate.

Situation: We manage a bunch of servers owned by different projects. Over 500 servers, maybe 40-50 projects, 4 to 20 servers on a given project.

We get requests to apply server patches & updates & installations. We have to track the requests by specific request name across all servers but we often have to report grouped by project as well as give details of a specific server. Many of my reports will be dynamically filtered based on some factors so we can report on single projects or single requests. The filtration is working but the group footer counts are not always cooperative.

So I've got a report that shows these groupings:

Report header: Titles and such, no real data.
Outer group header: Action name
Inner group header: Project name
Details: Server name and the current state of that action on that server
Inner group footer: Count of servers for the project
Outer group footer:
Report footer:

The base query is NOT a summation query because that same query will support maybe six to ten different reports that can be filtered by various criteria and can provide various amounts of details. It's a humongous JOIN based on a junction table with LOTS of translations implicit in the joining. I let the report impose the grouping based on report design, but it is always based on the same recordset.

What I want in the outer group footer is the count of distinct projects for which at least one server was involved. If the particular action didn't apply to ANY servers on that project, it wouldn't be shown on that segment of the report anyway. I don't want non-represented projects to be counted as a project affected by the request. That's not what I get, though.

I get a number equal to the count of all servers across all groups having that particular applicable action. I want to just count the number of groups, not the number of servers. It supplied me with "=Count(*)" when I used the report design tools to tell it I wanted an aggregate in the group footer. But there is no aggregate called "COUNT DISTINCT" so I couldn't select it. I've told it to count different fields (i.e. not * but Project Short Name) to see if it makes any difference, but since I cannot select "DISTINCT" on any of these, it doesn't help. I've tried counting records and counting values, but that doesn't help. The only saving grace is that when I imported the data from its point of origin, I filter out nulls, so no field is null in any of my tables.

For the report footer, I want the count of distinct requests, but I get the count of ALL servers across all projects and all requests. So again, no joy.

I've played around with the silly thing for a while and can't quite get that outer footer or the report footer quite right. That includes the running sum options (except I want non-running counts, not running sums.)

Obviously, I've forgotten something. So... what did I do wrong? I don't recall having this problem the last time I tried to do unique value counts in group footers. Can you help me with my brain cramp?

 

[Banana]

Would it be possible to divide & conquer and execute a separate query to obtain the count of distinct projects? If necessary, you could make it a function behind the report like:

=CountOfDistinctProjects([RequestID])

and call the query to grab the correct number?

 

[The_Doc_Man]

Might have to do something like that, but was hoping there was a built-in way to do this. I could SWEAR I've done this before, I just cannot recall how.

 

[Banana]

The problem with the built-in expression, as I see it, is that you want a different criteria from what you are doing in the report. If I am following you along, the report itself enumerates all servers affected by a certain request, but you want to count distinct projects that would be affected by the same request. Since that is a different question, it seems to me implying a need for a separate query (or at least a sub-query inside the source query if practical).

 

[The_Doc_Man]

I understand what you are saying but that seems self-defeating in an odd way. The point of having all those options for what can be included in report footers seems lost on me if I can't do an aggregate count that resets itself at the end of each group footer. I.e. what is the opposite of a running sum or count? Non-running?

Once we talk about non-cumulative counts or sums as an option (running sums yes or no), it seems that it ISN'T a different question for a report.