[AccessD] Rank in a query

Hi Mark

Try to reverse the aliasing:

SELECT 
  Quality, 
    (Select Count(*)
    FROM qryQ_Mo_Score As q
    WHERE q.Quality>qryQ_Mo_Score.[Quality];) AS 
  Ranking
FROM 
  qryQ_Mo_Score;

/gustav

>>> markamatte at hotmail.com 21-08-2008 21:20 >>>

Hello All,

I need to add a 'ranking' in a query.  I can do a subquery to count records greater than the value in question...this works fine againts a table...but I want to run it againts a query...but for some reason I get an error that it can't find my field/query?

Does a subquery referencing a table behave differently than a subquery referencing a query?

Thanks,

Mark A. Matte


**********THIS WORKS **************
SELECT a.Au_Score, (SELECT count(*)
FROM tblCase_Audit
WHERE Au_Score>[a].[au_score];) AS Expr1
FROM tblCase_Audit AS a;


**********THIS DOES NOT WORK **************
SELECT q.Quality, (Select Count(*)
 FROM qryQ_Mo_Score
WHERE Quality>[q].[Quality];) AS Ranking
FROM qryQ_Mo_Score AS q;

ERROR= "Does not recognize '[q].[Quality]'"