[AccessD] Rank in a query

Hi Mark

I see. Never thought of that.
I would guess that Access barks if it cannot identify _all_ columns.

/gustav

>>> markamatte at hotmail.com 22-08-2008 16:26 >>>

Gustav,

Thank You...This worked...but now I am confused in a different way...lol....

Here is the crosstab:
************SQL**************
TRANSFORM Avg(tblQuality_Score.Q_Score) AS AvgOfQ_Score
SELECT tblQuality_Score.Agent_ID, Avg(tblQuality_Score.Q_Score) AS Quality
FROM tblQuality_Score INNER JOIN tblcalendar ON tblQuality_Score.Doc_Date = tblcalendar.sears_date
GROUP BY tblQuality_Score.Agent_ID
PIVOT tblcalendar.month_abbr In ("Apr","May","Jun");
************SQL***************
The field I am trying to reference is Quality...which is NOT used in defining fixed column headings.  So by defining ("Apr","May","Jun") I can now reference "Quality"?

I know it works, glad it works, just curious why...

Thanks,

Mark


> Date: Fri, 22 Aug 2008 15:52:14 +0200
> From: Gustav at cactus.dk 
> To: accessd at databaseadvisors.com 
> Subject: Re: [AccessD] Rank in a query
>
> Hi Mark
>
> They certainly are.
> Try to specify Fixed Column Headings in the crosstab query.
>
>>>> markamatte at hotmail.com 22-08-2008 15:39>>>
>
> 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]'"