[AccessD] Rank in a query

Thanks Gustav,

But this did not work...I get the same error but reverse of the fields...

I think I forgot to mention 1 piece...qryQ_Mo_Score is a crosstab query([Quality] is a row heading not the value).  

Are crosstabs treated differently as record sources?

Thanks,

Mark


> Date: Thu, 21 Aug 2008 23:58:00 +0200
> From: Gustav at cactus.dk
> To: accessd at databaseadvisors.com
> Subject: Re: [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]'"
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com

_________________________________________________________________
Be the filmmaker you always wanted to be—learn how to burn a DVD with Windows®.
http://clk.atdmt.com/MRT/go/108588797/direct/01/