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

Is it possible to Aggregate an Aggregate Query?

twinnyfo
Expert Mod 100+
P: 2,443
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.
1 Week Ago #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+
10 Replies


NeoPa
Expert Mod 15k+
P: 30,869
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 :-(
1 Week Ago #2

NeoPa
Expert Mod 15k+
P: 30,869
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.
1 Week Ago #3

twinnyfo
Expert Mod 100+
P: 2,443
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.
1 Week Ago #4

Rabbit
Expert Mod 10K+
P: 12,230
I think a secondary query or subquery will be needed. I don't see a way of getting that lower level aggregate data otherwise.
1 Week Ago #5

NeoPa
Expert Mod 15k+
P: 30,869
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]
1 Week Ago #6

twinnyfo
Expert Mod 100+
P: 2,443
I’ll have to take a look at this next week. Overcome by too much activity at work.
1 Week Ago #7

NeoPa
Expert Mod 15k+
P: 30,869
That's fine Twinny - and thanks to Z for catching my [BOLD] tags slip-up ;-)
1 Week Ago #8

twinnyfo
Expert Mod 100+
P: 2,443
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!
5 Days Ago #9

NeoPa
Expert Mod 15k+
P: 30,869
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.
15 Hours Ago #10

twinnyfo
Expert Mod 100+
P: 2,443
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!
3 Hours Ago #11

Post your reply

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