Ranking Query Based on Aggregate

[Jeff Polack]

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;

 

[Jerry Whittle]

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 ;

 

[Jeff Polack]

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?

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 ;
--
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
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;

 

[Michael Gramelspacher]

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;

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;