Ranking Query Based on Aggregate

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jeff Polack - 26 Nov 2007 14:21 GMT

I've searched the boards and am perplexed. What is the best way to rank a
query that is ordered based on the descending value of a SumOf field? Code
that fails appears below.

TIA

---------------------------------
SELECT TOP 10 Q.Year, Q.[MANAGER-NAME], Sum(Q.ManagerFees) AS AllProducts,
(SELECT Count(*) FROM [qryMktShare-Domestic-LeagueTableMaster] Q1 WHERE
Q1.[MANAGER-NAME] = Q.[MANAGER-NAME] AND Sum(Q1.ManagerFees)<
Sum(Q.AllProducts)) AS Rank
FROM [qryMktShare-Domestic-LeagueTableMaster] AS Q
GROUP BY Q.Year, Q.[MANAGER-NAME]
HAVING (((Q.[MANAGER-NAME])<>"UNDISCLOSED") And
((Q.Year)=Forms!frmInvisible!txtReportYear))
ORDER BY Sum(Q.ManagerFees) DESC;

Reply to this Message

Jerry Whittle - 26 Nov 2007 17:03 GMT

How did it fail? What was the errro message or was there even an error message?

I'm guessing that you need to also group by Rank like below:

SELECT TOP 10 Q.Year,
Q.[MANAGER-NAME],
Sum(Q.ManagerFees) AS AllProducts,
(SELECT Count(*)
FROM [qryMktShare-Domestic-LeagueTableMaster] Q1
WHERE Q1.[MANAGER-NAME] = Q.[MANAGER-NAME]
AND Sum(Q1.ManagerFees)< Sum(Q.AllProducts)) AS Rank
FROM [qryMktShare-Domestic-LeagueTableMaster] AS Q
GROUP BY Q.Year, Q.[MANAGER-NAME], Rank
HAVING (((Q.[MANAGER-NAME])<>"UNDISCLOSED")
And ((Q.Year)=Forms!frmInvisible!txtReportYear))
ORDER BY Sum(Q.ManagerFees) DESC;

Then there's the problem that Year is a reserved word so you might want to
put brackets around it like [Year] .

When in doubt about SQL statement, I start by going from the simple to the
complex.

Does the following return anything?
SELECT Count(*)
FROM [qryMktShare-Domestic-LeagueTableMaster] Q1
WHERE Q1.[MANAGER-NAME] = Q.[MANAGER-NAME]
AND Sum(Q1.ManagerFees)< Sum(Q.AllProducts);

What happens when you take that part out like so?
SELECT TOP 10 Q.Year,
Q.[MANAGER-NAME],
Sum(Q.ManagerFees) AS AllProducts
FROM [qryMktShare-Domestic-LeagueTableMaster] AS Q
GROUP BY Q.Year, Q.[MANAGER-NAME]
HAVING (((Q.[MANAGER-NAME])<>"UNDISCLOSED")
And ((Q.Year)=Forms!frmInvisible!txtReportYear))
ORDER BY Sum(Q.ManagerFees) DESC;

Or something really simple?
SELECT Q.[Year],
Q.[MANAGER-NAME],
Q.ManagerFees
FROM [qryMktShare-Domestic-LeagueTableMaster] AS Q
HAVING Q.[MANAGER-NAME]<>"UNDISCLOSED"
And Q.[Year]=Forms!frmInvisible!txtReportYear ;

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> I've searched the boards and am perplexed. What is the best way to rank a
> query that is ordered based on the descending value of a SumOf field? Code
[quoted text clipped - 12 lines]
> ((Q.Year)=Forms!frmInvisible!txtReportYear))
> ORDER BY Sum(Q.ManagerFees) DESC;

Reply to this Message

Jeff Polack - 26 Nov 2007 17:18 GMT

Thanks for your reply. I'll study it closely.

The error message received appears below:

Cannot have aggregate function in WHERE clause (Q1.[MANAGER-NAME] =
Q.[MANAGER-NAME] AND SUM(Q1.ManagerFees)< Sum(Q.AllProducts)).

> How did it fail? What was the errro message or was there even an error message?
>
[quoted text clipped - 59 lines]
> > ((Q.Year)=Forms!frmInvisible!txtReportYear))
> > ORDER BY Sum(Q.ManagerFees) DESC;

Reply to this Message

Michael Gramelspacher - 26 Nov 2007 20:30 GMT

>I've searched the boards and am perplexed. What is the best way to rank a
>query that is ordered based on the descending value of a SumOf field? Code
[quoted text clipped - 12 lines]
>((Q.Year)=Forms!frmInvisible!txtReportYear))
>ORDER BY Sum(Q.ManagerFees) DESC;

maybe (untested):

SELECT TOP 10 *
FROM (SELECT Q.YEAR,
Q.[MANAGER-NAME],
SUM(Q.ManagerFees) AS AllProducts
FROM [qryMktShare-Domestic-LeagueTableMaster] AS Q
WHERE (((Q.[MANAGER-NAME]) <> "UNDISCLOSED")
AND ((Q.YEAR) = Forms!frmInvisible!txtReportYear)
GROUP BY Q.YEAR,Q.[MANAGER-NAME]) AS a
ORDER BY a.AllProducts DESC;

Reply to this Message