423,682 Members | 1,425 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,682 IT Pros & Developers. It's quick & easy.

Is it possible to Aggregate an Aggregate Query?

twinnyfo
Expert Mod 2.5K+
P: 2,667
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
  1. SELECT BoardIDKey, CC, NAF, SRID, 
  2.        Sum(IIf(Zone=3, 1, 0)) AS B, 
  3.        BPZDPRate, SRDP(B, BPZDPRate) AS SRDP, 
  4.        IIf(SRDP = 0, B, 0) AS BPZAgg 
  5. FROM (tblEligibles 
  6.     INNER JOIN (tblBoards 
  7.         INNER JOIN tblBoardIDs 
  8.             ON tblBoards.BoardID = tblBoardIDs.BoardIDKey) 
  9.         ON tblEligibles.BoardID = tblBoards.BoardID 
  10.             AND tblEligibles.CC = tblBoards.CC) 
  11.     INNER JOIN ((SELECT SR, NAF 
  12.                  FROM tblSeniorRaters 
  13.                  WHERE ((Actual) AND (NOT Projected))) AS tblSRs) 
  14.         ON tblEligibles.SRID = tblSRs.SR 
  15. GROUP BY BoardIDKey, CC, NAF, SRID, BPZDPRate;
This produces the following results:
Expand|Select|Wrap|Line Numbers
  1. ID    CC NAF  SRID    B   BPZ Rate  SRDP  BPZAgg
  2. 246   1   1   1C110   2   10.00%    0     2
  3. 246   1   1   1C11A   1   10.00%    0     1
  4. 246   1   1   1C11C   19  10.00%    1     0
  5. 246   1   1   1C11D   1   10.00%    0     1
  6. 246   1   1   1C11E   8   10.00%    0     8
  7. 246   1   1   1C11F   2   10.00%    0     2
  8. 246   1   1   1C11G   2   10.00%    0     2
  9. 246   1   1   1C11H   4   10.00%    0     4
  10. 246   1   1   1C11N   2   10.00%    0     2
  11. 246   1   1   1C11R   2   10.00%    0     2
  12. 246   1   2   1C770   4   10.00%    0     4
  13. 246   1   2   1C771   78  10.00%    7     0
  14. 246   1   2   1C772   56  10.00%    5     0
  15. 246   1   2   1C773   3   10.00%    0     3
  16. 246   1   2   1C774   1   10.00%    0     1
  17. 246   1   2   1C775   10  10.00%    1     0
  18. 246   1   3   1CCE0   7   10.00%    0     7
  19. 246   1   3   1CCEA   4   10.00%    0     4
  20. 246   1   3   1CCEB   2   10.00%    0     2
  21. 246   1   4   1C220   2   10.00%    0     2
  22. 246   1   4   1C221   0   10.00%    0     0
  23. 246   1   5   1C9A0   6   10.00%    0     6
  24. 246   1   5   1C9AA   3   10.00%    0     3
  25. 246   1   5   1C9AB   22  10.00%    2     0
  26. 246   1   5   1C9AC   7   10.00%    0     7
  27. 246   1   5   1C9AD   22  10.00%    2     0
  28. 246   1   5   1C9AF   9   10.00%    0     9
  29. 246   1   5   1C9AG   1   10.00%    0     1
  30. 246   1   5   1C9AH   24  10.00%    2     0
  31. 246   1   5   1C9AJ   6   10.00%    0     6
  32. 246   1   6   1C660   22  10.00%    2     0
  33. 246   1   6   1C661   40  10.00%    4     0
  34. 246   1   6   1C664   24  10.00%    2     0
  35. 246   1   6   1C665   4   10.00%    0     4
  36. 246   1   6   1C66C   34  10.00%    3     0
  37. 246   1   6   1C66D   6   10.00%    0     6
  38. 246   1   6   1C66G   30  10.00%    3     0
  39. 246   1   6   1C66J   8   10.00%    0     8
  40. 246   1   7   1C250   4   10.00%    0     4
  41. 246   1   7   1C25A   43  10.00%    4     0
  42. 246   1   7   1C25B   27  10.00%    2     0
  43. 246   1   7   1C25C   28  10.00%    2     0
  44. 246   1   7   1C25D   9   10.00%    0     9
  45. 246   1   7   1C25E   14  10.00%    1     0
  46. 246   1   7   1C25F   9   10.00%    0     9
  47. 246   1   7   1C25G   4   10.00%    0     4
The second Query, qryBPZRollupByNAF, looks like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT BoardIDKey, CC, NAF, 
  2.        Sum(IIf(SRDP=0,BPZAgg,0)) AS NAFAgg, 
  3.        BPZDPRate, 
  4.        IIf([NAF]=1, 0, SRDP(NAFAgg,BPZDPRate)) AS NAFBPZDP, 
  5.        IIf(NAF=1, NAFAgg, IIf(NAFBPZDP=0, NAFAgg, 0)) AS MAJAgg
  6. FROM qryBPZRollupBySR
  7. 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
  1. ID   CC  NAF  NAFAgg  BPZ Rate  NAFBPZDP  MAJAgg
  2. 246   1   1    24      10.00%      0        24
  3. 246   1   2     8      10.00%      0         8
  4. 246   1   3    13      10.00%      1         0
  5. 246   1   4     2      10.00%      0         2
  6. 246   1   5    32      10.00%      3         0
  7. 246   1   6    18      10.00%      1         0
  8. 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

✓ answered by 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.

Share this Question
Share on Google+
12 Replies


NeoPa
Expert Mod 15k+
P: 31,084
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

NeoPa
Expert Mod 15k+
P: 31,084
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

twinnyfo
Expert Mod 2.5K+
P: 2,667
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

Rabbit
Expert Mod 10K+
P: 12,272
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

NeoPa
Expert Mod 15k+
P: 31,084
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
  1. Query [qryBPZRollupBySR]
  2.  
  3. SELECT   [tblBoardIDs].[BoardIDKey]
  4.        , [tblEligibles].[CC]
  5.        , [tblSRs].[NAF]
  6.        , [tblEligibles].[SRID]
  7.        , Sum(IIf([Zone]=3,1,0)) AS [B]
  8.        , [BPZDPRate]
  9.        , SRDP([B],[BPZDPRate]) AS [SRDP]
  10.        , IIf([SRDP]=0,[B],0) AS [BPZAgg]
  11. FROM     ([tblEligibles]
  12.           INNER JOIN
  13.           ([tblBoards]
  14.            INNER JOIN
  15.            [tblBoardIDs]
  16.   ON       [tblBoards].[BoardID]=[tblBoardIDs].[BoardIDKey])
  17.   ON      [tblEligibles].[BoardID]=[tblBoards].[BoardID]
  18.  AND      [tblEligibles].[CC]=[tblBoards].[CC])
  19.          INNER JOIN
  20.          (SELECT [SR]
  21.                , [NAF]
  22.           FROM   [tblSeniorRaters]
  23.           WHERE  (([Actual])
  24.             AND   (NOT [Projected]))) AS [tblSRs])
  25.   ON     [tblEligibles].[SRID]=[tblSRs].[SR]
  26. GROUP BY [tblBoardIDs].[BoardIDKey]
  27.        , [tblEligibles].[CC]
  28.        , [tblSRs].[NAF]
  29.        , [tblEligibles].[SRID]
  30.        , [BPZDPRate]
Jul 12 '18 #6

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

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

twinnyfo
Expert Mod 2.5K+
P: 2,667
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
  1. SELECT   tblBoardIDs.BoardIDKey
  2.        , tblEligibles.CompCat
  3.        , tblSRs.NAF
  4.        , tblEligibles.SRID
  5.        , Sum(IIf([Zone]=3,1,0)) AS B
  6.        , tblBoards.BPZDPRate
  7.        , SRDP([b],[BPZDPRate]) AS SRDP
  8.        , IIf([SRDP]=0,[b],0) AS BPZAgg
  9. FROM     tblBoardIDs 
  10.          INNER JOIN 
  11.        ((tblEligibles 
  12.               INNER JOIN tblBoards 
  13. ON      (tblEligibles.BoardID = tblBoards.BoardID) 
  14. AND     (tblEligibles.CompCat = tblBoards.CompCat)) 
  15.          INNER JOIN 
  16.         (SELECT SRID
  17.               , NAF 
  18.          FROM tblSeniorRaters 
  19.          WHERE (Actual 
  20.            AND (NOT Projected))  AS tblSRs 
  21. ON       tblEligibles.SRID = tblSRs.SRID) 
  22. ON       tblBoardIDs.BoardIDKey = tblBoards.BoardID
  23. GROUP BY tblBoardIDs.BoardIDKey
  24.        , tblEligibles.CompCat
  25.        , tblSRs.NAF
  26.        , tblEligibles.SRID
  27.        , tblBoards.BPZDPRate;
This works.

Thanks for looking into this!
Jul 18 '18 #9

NeoPa
Expert Mod 15k+
P: 31,084
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

twinnyfo
Expert Mod 2.5K+
P: 2,667
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!
Jul 23 '18 #11

P: 47
hi twinnyfo

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

cheers!
Jul 23 '18 #12

twinnyfo
Expert Mod 2.5K+
P: 2,667
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!
Jul 23 '18 #13

Post your reply

Sign in to post your reply or Sign up for a free account.