435,315 Members | 2,150 Online
Need help? Post your question and get tips & solutions from a community of 435,315 IT Pros & Developers. It's quick & easy.

# Is it possible to Aggregate an Aggregate Query?

 Expert Mod 2.5K+ P: 3,284 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: Expand|Select|Wrap|Line Numbers 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: Expand|Select|Wrap|Line Numbers 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: Expand|Select|Wrap|Line Numbers 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: Expand|Select|Wrap|Line Numbers 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. Jul 11 '18 #1

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.

12 Replies

 Expert Mod 15k+ P: 31,494 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 :-( Jul 11 '18 #2

 Expert Mod 15k+ P: 31,494 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. Jul 11 '18 #3

 Expert Mod 2.5K+ P: 3,284 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. Jul 11 '18 #4

 Expert Mod 10K+ P: 12,366 I think a secondary query or subquery will be needed. I don't see a way of getting that lower level aggregate data otherwise. Jul 11 '18 #5

 Expert Mod 15k+ P: 31,494 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. Expand|Select|Wrap|Line Numbers 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] Jul 12 '18 #6

 Expert Mod 2.5K+ P: 3,284 I’ll have to take a look at this next week. Overcome by too much activity at work. Jul 13 '18 #7

 Expert Mod 15k+ P: 31,494 That's fine Twinny - and thanks to Z for catching my [BOLD] tags slip-up ;-) Jul 13 '18 #8

 Expert Mod 2.5K+ P: 3,284 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): Expand|Select|Wrap|Line Numbers 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! Jul 18 '18 #9

 Expert Mod 15k+ P: 31,494 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. Jul 22 '18 #10