Please Help!! Nested Aggregate Function

[TracyDKY]

I have a union query that tells me all of my offices - 800 offices with a total of 9000 employees. It is a totals table so all I see is the total number of employees. I have it tied with a table that lists employees that have received rewards, currently 294. I built a union query so I can get a list of all offices and who has received rewards. I'm then getting a total count of employees broken down by manager with a percentage. The problem:

I'm using the Sum([CountofSSN] to give me the total of employees for each managers area. The problem is when there are award recipients it counts that total a multiple of times. The records are already in groups.

I've tried the following to get the acutal count:
=Sum(([CountofSSN])-(((Count([RecipSSN]))-1)*[CountofSSN])) but you can't use an aggregate nested in another aggregate.

ANY SUGGESTIONS WILL HELP!

 

[zambezibill]

Since your UNION query is as you say, just providing employee totals, it is irrelevant. To provide specifics we will need to know the structure of your table(s).

And just so later I can say "told you so", SSN numbers are a poor choice to identify records.

Cheers, Bill