Top Values View- Please Help

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I have a query that i'm trying to get the top 5 sales rep
for. i set the Sort to descending and i set the Top value
to 5% but when i run the query i still see every field
instead of the top 5%. Even when i try different values
like 5 without the % sign or higher numbers like 25, i
still see all fields. Please assist, i'm not sure what
i've done wrong.

Tom
 
Here is my script.I'm not sure if it will not allow me to
do it because i'm using expressions or totals. Any
assistance, would be appreciated.

SELECT DISTINCTROW TOP 5 PERCENT Sum([Errorpts1])/3/Count
([Count]) AS Expr1, Sum([Errorpts2])/8/Count([Count]) AS
Expr2, Sum([Errorpts3])/12/Count([Count]) AS Expr3, Sum
([Errorpts4])/4/Count([Count]) AS Expr4, Sum
([Errorpts5])/15/Count([Count]) AS Expr5, Sum
([Errorpts6])/4/Count([Count]) AS Expr6, Sum
([Errorpts7])/13/Count([Count]) AS Expr7, Sum
([Errorpts8])/7/Count([Count]) AS Expr8, Sum
([Errorpts9])/11/Count([Count]) AS Expr9, Sum
([Errorpts10])/4/Count([Count]) AS Expr10, Sum
([Errorpts11])/5/Count([Count]) AS Expr11, Sum
([Errorpts12])/3/Count([Count]) AS Expr12, Sum
([Errorpts13])/5/Count([Count]) AS Expr13, Sum
([Errorpts14])/3/Count([Count]) AS Expr14, Sum
([Errorpts15])/3/Count([Count]) AS Expr15
FROM [Enrollment Services QA Form: Query]
WHERE ((([Enrollment Services QA Form: Query].ReportDate)
Between [Type the beginning ReportDate:] And [Type the
ending ReportDate:]))
ORDER BY Sum([Errorpts1])/3/Count([Count]) DESC , Sum
([Errorpts2])/8/Count([Count]) DESC , Sum
([Errorpts3])/12/Count([Count]) DESC , Sum
([Errorpts4])/4/Count([Count]) DESC , Sum
([Errorpts5])/15/Count([Count]) DESC , Sum
([Errorpts6])/4/Count([Count]) DESC , Sum
([Errorpts7])/13/Count([Count]) DESC , Sum
([Errorpts8])/7/Count([Count]) DESC , Sum
([Errorpts9])/11/Count([Count]) DESC , Sum
([Errorpts10])/4/Count([Count]) DESC , Sum
([Errorpts11])/5/Count([Count]) DESC , Sum
([Errorpts12])/3/Count([Count]) DESC , Sum
([Errorpts13])/5/Count([Count]) DESC , Sum
([Errorpts14])/3/Count([Count]) DESC , Sum
([Errorpts15])/3/Count([Count]) DESC;
 
Try dissecting your Query..

First remove the "TOP 5 PERCENT"...what do you see

Then the "DISTINCTROW"...what do you see

Bo
 
When i take out the TOP 5 PERCENT or DISTINCTROW, there is
no change.. i changed SELECT TO UPDATE TO PROCEDURE and
got syntax errors. any other suggestions?

Thanks,
Tom
 
Looking at..
Sum([Errorpts1])/3/Coun
([Count]) AS Expr1,...please try an additional set of Parens like..

Sum(([Errorpts1])/3)/(Coun
([Count])) AS Expr

This would have to be done on all your other "AS" Expressions

Bo
 
Back
Top