Top 25 Brokers for each MMPM query

[Pat Dools]

Hello,

I am trying to write a query that will show me the Top 25 Brokers (by
'TotalPremium') for each Broker Manager ('MMPM', in my case). I have the
following query, but it only supplies the Top 25 for the first MMPM
(alphabetically) in my list:

SELECT TOP 25 rpt_MNTHLY_YTD_PC_HIT_RATIO.MMPM,
rpt_MNTHLY_YTD_PC_HIT_RATIO.BROKER_NAME,
rpt_MNTHLY_YTD_PC_HIT_RATIO.BROKER_ADDRESS,
rpt_MNTHLY_YTD_PC_HIT_RATIO.BROKER_CITY,
rpt_MNTHLY_YTD_PC_HIT_RATIO.BROKER_STATE,
Sum(rpt_MNTHLY_YTD_PC_HIT_RATIO.POL_NEW) AS SumOfPOL_NEW,
Sum(rpt_MNTHLY_YTD_PC_HIT_RATIO.POL_PREMIUM_NEW) AS SumOfPOL_PREMIUM_NEW,
Sum(rpt_MNTHLY_YTD_PC_HIT_RATIO.POL_RNWL) AS SumOfPOL_RNWL,
Sum(rpt_MNTHLY_YTD_PC_HIT_RATIO.POL_PREMIUM_RNWL) AS SumOfPOL_PREMIUM_RNWL,
Sum([POL_PREMIUM_NEW]+[POL_PREMIUM_RNWL]) AS TotalPremium,
Sum(rpt_MNTHLY_YTD_PC_HIT_RATIO.NOT_QUOTED_COUNT) AS SumOfNOT_QUOTED_COUNT,
Sum(rpt_MNTHLY_YTD_PC_HIT_RATIO.QT_COUNT) AS SumOfQT_COUNT,
Sum(rpt_MNTHLY_YTD_PC_HIT_RATIO.DECLINE_COUNT) AS SumOfDECLINE_COUNT,
Sum(rpt_MNTHLY_YTD_PC_HIT_RATIO.RNWL_QUOTE_COUNT) AS SumOfRNWL_QUOTE_COUNT
FROM rpt_MNTHLY_YTD_PC_HIT_RATIO
GROUP BY rpt_MNTHLY_YTD_PC_HIT_RATIO.MMPM,
rpt_MNTHLY_YTD_PC_HIT_RATIO.BROKER_NAME,
rpt_MNTHLY_YTD_PC_HIT_RATIO.BROKER_ADDRESS,
rpt_MNTHLY_YTD_PC_HIT_RATIO.BROKER_CITY,
rpt_MNTHLY_YTD_PC_HIT_RATIO.BROKER_STATE
ORDER BY rpt_MNTHLY_YTD_PC_HIT_RATIO.MMPM,
Sum([POL_PREMIUM_NEW]+[POL_PREMIUM_RNWL]) DESC;

How can i get the Top 25 for EACH 'MMPM' to show in my results?

I also tried to do a Union query, where I just put the name of each MMPM in
the WHERE clause, but it complains about the
'Sum([POL_PREMIUM_NEW]+[POL_PREMIUM_RNWL])' piece of the ORDER BY clause, but
only for the second part of the Union query, which doesn't make sense to me,
either.

Thanks,

 

[scubadiver]

Are you aware of a "top" option so you can select certain records?

In the top pane of the query design, right click and go to "properties",
there is an option for top values. See if that helps.