Is it possible to Aggregate an Aggregate Query?

[twinnyfo]

OK Fellow Byte Mates,

Here might be a complicated one or an easy one, but at this point it is beyond my current skill level. The explanation might be a bit lengthy, but I will place the question up front.

I have a query that aggregates values at one level. I have another query that aggregates certain values from that first query at another level. Is there a way to combine those two queries into one query, so that I get the same results, but with only one query?

I think the answer is "No", but I will ask the question and provide details of these queries below. I also apologize in advance for the length of this question, but I think I have the right audience to analyze the problem.

Background
I work in the officer promotions world in the Air Force and I deal with counting beans that are eligible for promotion. We apply certain percentages to the number of people to determine how many quotas a wing commander (represented by a code, called a SRID, below) has to offer their strongest recommendations. If the wing commander does not earn a quota, their number of eligible officers is aggregated to the next higher level (the Numbered Air Force--NAF). The total number of eligibles aggregated at the NAF Level have the same percentage applied and they may earn quotas, based on the total population. If a NAF does not earn a quota, all their eligibles are aggregated at the next higher level (the Major Command--MAJCOM).

How does this look in Queries? The first Query, qryBPZRollupBySR, looks like this:

SELECT BoardIDKey, CC, NAF, SRID, 
       Sum(IIf(Zone=3, 1, 0)) AS B, 
       BPZDPRate, SRDP(B, BPZDPRate) AS SRDP, 
       IIf(SRDP = 0, B, 0) AS BPZAgg 
FROM (tblEligibles 
    INNER JOIN (tblBoards 
        INNER JOIN tblBoardIDs 
            ON tblBoards.BoardID = tblBoardIDs.BoardIDKey) 
        ON tblEligibles.BoardID = tblBoards.BoardID 
            AND tblEligibles.CC = tblBoards.CC) 
    INNER JOIN ((SELECT SR, NAF 
                 FROM tblSeniorRaters 
                 WHERE ((Actual) AND (NOT Projected))) AS tblSRs) 
        ON tblEligibles.SRID = tblSRs.SR 
GROUP BY BoardIDKey, CC, NAF, SRID, BPZDPRate;

This produces the following results:

ID    CC NAF  SRID    B   BPZ Rate  SRDP  BPZAgg
246   1   1   1C110   2   10.00%    0     2
246   1   1   1C11A   1   10.00%    0     1
246   1   1   1C11C   19  10.00%    1     0
246   1   1   1C11D   1   10.00%    0     1
246   1   1   1C11E   8   10.00%    0     8
246   1   1   1C11F   2   10.00%    0     2
246   1   1   1C11G   2   10.00%    0     2
246   1   1   1C11H   4   10.00%    0     4
246   1   1   1C11N   2   10.00%    0     2
246   1   1   1C11R   2   10.00%    0     2
246   1   2   1C770   4   10.00%    0     4
246   1   2   1C771   78  10.00%    7     0
246   1   2   1C772   56  10.00%    5     0
246   1   2   1C773   3   10.00%    0     3
246   1   2   1C774   1   10.00%    0     1
246   1   2   1C775   10  10.00%    1     0
246   1   3   1CCE0   7   10.00%    0     7
246   1   3   1CCEA   4   10.00%    0     4
246   1   3   1CCEB   2   10.00%    0     2
246   1   4   1C220   2   10.00%    0     2
246   1   4   1C221   0   10.00%    0     0
246   1   5   1C9A0   6   10.00%    0     6
246   1   5   1C9AA   3   10.00%    0     3
246   1   5   1C9AB   22  10.00%    2     0
246   1   5   1C9AC   7   10.00%    0     7
246   1   5   1C9AD   22  10.00%    2     0
246   1   5   1C9AF   9   10.00%    0     9
246   1   5   1C9AG   1   10.00%    0     1
246   1   5   1C9AH   24  10.00%    2     0
246   1   5   1C9AJ   6   10.00%    0     6
246   1   6   1C660   22  10.00%    2     0
246   1   6   1C661   40  10.00%    4     0
246   1   6   1C664   24  10.00%    2     0
246   1   6   1C665   4   10.00%    0     4
246   1   6   1C66C   34  10.00%    3     0
246   1   6   1C66D   6   10.00%    0     6
246   1   6   1C66G   30  10.00%    3     0
246   1   6   1C66J   8   10.00%    0     8
246   1   7   1C250   4   10.00%    0     4
246   1   7   1C25A   43  10.00%    4     0
246   1   7   1C25B   27  10.00%    2     0
246   1   7   1C25C   28  10.00%    2     0
246   1   7   1C25D   9   10.00%    0     9
246   1   7   1C25E   14  10.00%    1     0
246   1   7   1C25F   9   10.00%    0     9
246   1   7   1C25G   4   10.00%    0     4

The second Query, qryBPZRollupByNAF, looks like this:

SELECT BoardIDKey, CC, NAF, 
       Sum(IIf(SRDP=0,BPZAgg,0)) AS NAFAgg, 
       BPZDPRate, 
       IIf([NAF]=1, 0, SRDP(NAFAgg,BPZDPRate)) AS NAFBPZDP, 
       IIf(NAF=1, NAFAgg, IIf(NAFBPZDP=0, NAFAgg, 0)) AS MAJAgg
FROM qryBPZRollupBySR
GROUP BY BoardIDKey, CC, NAF, BPZDPRate;

You will note that the records only go forward to the NAF level if the SRID does not earn a quota (Sum(IIf(SRDP=0,BPZAgg,0)) AS NAFAgg). This is the real hitch in these queries.

This Query produces the following results:

ID   CC  NAF  NAFAgg  BPZ Rate  NAFBPZDP  MAJAgg
246   1   1    24      10.00%      0        24
246   1   2     8      10.00%      0         8
246   1   3    13      10.00%      1         0
246   1   4     2      10.00%      0         2
246   1   5    32      10.00%      3         0
246   1   6    18      10.00%      1         0
246   1   7    26      10.00%      2         0

I must also emphasize that, as currently written, these Queries work perfectly and quickly, and at this point, this question is based more on curiosity than necessity. If the results in the second Query can't be achieved through only one Query, then the mission will go on. However it would be nice if I could streamline the query a bit.

Thanks for reading, and I am always open to suggestions and improvements.

[NeoPa]

Busy time coming up, but after a quick read it seems to me that it should be possible to handle these aggregations and bring them together into a single Query.

I may be away a day or two though :-(

[NeoPa]

Just grabbed 5 mins.

What does SRDP() do for you?

When you ask for a single query are you talking about [qryBPZRollupBySR] being included within [qryBPZRollupByNAF] as SQL?

Alternatively, are you talking about the overall effect being reached as a single-level query, directly from the [tblBoards], [tblBoardIDs] and [tblSeniorRaters] tables? That I can imagine being impossible, though I can't say for sure at this stage.

[twinnyfo]

SRDP() calculates the quotas. It is a complex process not simply being a calculation of (number x percentage). I found using a user-defined function was "easier" and cleaner.

Concerning your second question, I think my "intent" is to write one query, without sub-sub-queries, that just derives the end result.

Again, because the subsequent query [qryBPZRollupByNAF] only aggregates some of the values, based on certain criteria, I'm not sure that is possible. However, methinks that if such a monster could be created, I would learn a termendous deal about queries.

[Rabbit]

I think a secondary query or subquery will be needed. I don't see a way of getting that lower level aggregate data otherwise.

[NeoPa]

I don't believe you need a sub-sub-query. However, while working on the SQL for [qryBPZRollupBySR] I found things which would make it not work. I assume you have tried to format it for legibility and simply introduced a couple of ambiguities that Jet/ACE aren't able to cope with.

I've tried to reformat it below, but there are items which aren't clear where they've come from and others which were ambiguous so I've just guessed. Please confirm that all my guesses are acceptable and explain which tables the other fields come from so that I can have a long think about how best I would handle your eventual query.

Query [qryBPZRollupBySR]
 
SELECT   [tblBoardIDs].[BoardIDKey]
       , [tblEligibles].[CC]
       , [tblSRs].[NAF]
       , [tblEligibles].[SRID]
       , Sum(IIf([Zone]=3,1,0)) AS [B]
       , [BPZDPRate]
       , SRDP([B],[BPZDPRate]) AS [SRDP]
       , IIf([SRDP]=0,[B],0) AS [BPZAgg]
FROM     ([tblEligibles]
          INNER JOIN
          ([tblBoards]
           INNER JOIN
           [tblBoardIDs]
  ON       [tblBoards].[BoardID]=[tblBoardIDs].[BoardIDKey])
  ON      [tblEligibles].[BoardID]=[tblBoards].[BoardID]
 AND      [tblEligibles].[CC]=[tblBoards].[CC])
         INNER JOIN
         (SELECT [SR]
               , [NAF]
          FROM   [tblSeniorRaters]
          WHERE  (([Actual])
            AND   (NOT [Projected]))) AS [tblSRs])
  ON     [tblEligibles].[SRID]=[tblSRs].[SR]
GROUP BY [tblBoardIDs].[BoardIDKey]
       , [tblEligibles].[CC]
       , [tblSRs].[NAF]
       , [tblEligibles].[SRID]
       , [BPZDPRate]

[twinnyfo]

I’ll have to take a look at this next week. Overcome by too much activity at work.

[NeoPa]

That's fine Twinny - and thanks to Z for catching my [BOLD] tags slip-up ;-)

[twinnyfo]

Finally got back to this one. There were a few inconsistencies which I had to fix to make it actually work within the confines of reducing confusion (and some of my fault, too). And, Access likes to rearrange the order of tables in the FROM clause when using the editor....

qryBPZRollupBySR (formatted per your fashion):

SELECT   tblBoardIDs.BoardIDKey
       , tblEligibles.CompCat
       , tblSRs.NAF
       , tblEligibles.SRID
       , Sum(IIf([Zone]=3,1,0)) AS B
       , tblBoards.BPZDPRate
       , SRDP([b],[BPZDPRate]) AS SRDP
       , IIf([SRDP]=0,[b],0) AS BPZAgg
FROM     tblBoardIDs 
         INNER JOIN 
       ((tblEligibles 
              INNER JOIN tblBoards 
ON      (tblEligibles.BoardID = tblBoards.BoardID) 
AND     (tblEligibles.CompCat = tblBoards.CompCat)) 
         INNER JOIN 
        (SELECT SRID
              , NAF 
         FROM tblSeniorRaters 
         WHERE (Actual 
           AND (NOT Projected))  AS tblSRs 
ON       tblEligibles.SRID = tblSRs.SRID) 
ON       tblBoardIDs.BoardIDKey = tblBoards.BoardID
GROUP BY tblBoardIDs.BoardIDKey
       , tblEligibles.CompCat
       , tblSRs.NAF
       , tblEligibles.SRID
       , tblBoards.BPZDPRate;

This works.

Thanks for looking into this!

[NeoPa]

Hi Twinny.

Sorry for the delay. I knew this would take time and concentration so I couldn't just jump on when I had a couple of minutes to spare.

So, taking [tblEligibles].[CompCat] to be what you later refer to as [CC] in your other aggregation query (from OP), I would say that I don't believe it's possible to produce that result without an extra level of aggregation. That's because with the original grouping you include [tblEligibles].[SRID]. At that level of grouping you execute your code SRDP([b],[BPZDPRate]) AS SRDP (Essentially black box.) so you have no way of determining that result unless you group at that level. After that you need to group by [tblEligibles].[SRID].

It seems to me that you either have to have a separate QueryDef for [qryBPZRollupBySR], or include the SQL for it as a subquery in the SQL for your other query.

[twinnyfo]

All,

Thanks for taking a look at this one. I think y'all's conclusion is the same as mine. I will mark NeoPa's latest response as the "Best Answer," even though it did not resolve the problem, and only reinforced other comments and inclinations throughout the thread.

Just as an FYI, I had to re-create this query (and others based upon it) due to some potential policy changes in how the Air Force might look at officer promotions (that's about all I can say about that). My re-work was an attempt to launch a pre-emptive strike on those changes, but at the same time, produce queries that were more streamlined and straightforward with the particular tasks at hand. I will say that this task is accomplished--even though I did not do it in one query. Ultimately, to suit the needs of the entire Project I am working on, it took six queries (instead of about 12), and they run more quickly than the old ones.

For those of you who might be newer to Bytes or newer to MS Access, this thread is a perfect example of experts simply throwing ideas around in hopes of finding a better way to do things. Sometimes we succeed and other times, we are prevented from success because of the nature of our intended results. That is not failure. It is simply learning to live within the constraints of the environment in which we work.

Thanks again to all!

[molly00mayhem]

hi twinnyfo

yup. havent a clue about the code but just watching everybody brainstorm has been fun and instructive. just mollys 2 pfennig!

cheers!

[twinnyfo]

Molly,

I spend quite a bit of time just "trolling" the MS Access/VBA forum to see what is coming up. Even if it's stuff I don't/won't/can't use, sometimes it's helpful to know that I could use it.

Grace and peace!