John-
(Am I talking to myself? <s>)
Just reverse the criteria in the subquery:
SELECT tblTopTenFinalResults.[Measure ID], tblTopTenFinalResults.Rate,
tblTopTenFinalResults.OrgID,
(SELECT COUNT(*) FROM
tblTopTenFinalResults As Q
WHERE Q.[Measure ID] = tblTopTenFinalResults.[Measure ID]
AND Q.[Rate] > tblTopTenFinalResults.[Rate]) +1 AS Rank
FROM tblTopTenFinalResults
ORDER BY tblTopTenFinalResults.[Measure ID], tblTopTenFinalResults.Rate DESC;
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of jfakes.rm
Sent: Monday, July 25, 2011 4:18 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Ranking multiple Rates
John,
This is exactly what I am trying to do. The only problem is the highest rate
which should be #1 is ranked #10. I have been playing with sorting the table in
the query so the highest rate is #1 but I haven't been able to sort it
correctly.
Any suggestions?
John
--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Jfakes-
>
> You can return only one field in a subquery in a SELECT clause. I suspect
what
> you want to do is:
>
> SELECT tblTopTenFinalResults.[Measure ID], tblTopTenFinalResults.Rate,
> tblTopTenFinalResults.OrgID,
> (SELECT COUNT(*) FROM
> tblTopTenFinalResults As Q
> WHERE Q.[Measure ID] = tblTopTenFinalResults.[Measure ID]
> AND Q.[Rate] < tblTopTenFinalResults.[Rate]) +1 AS Rank
> FROM tblTopTenFinalResults
> ORDER BY tblTopTenFinalResults.[Measure ID], tblTopTenFinalResults.Rate DESC;
>
> That should get you the Rank of each row within the row's Measure ID.
>
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of jfakes.rm
> Sent: Friday, July 22, 2011 8:20 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Ranking multiple Rates
>
> This forum has been so helpful helping me get several queries built I really
> appreciate it.
>
> Here is the new problem:
>
> I created a tblTopTenFinalResults. In this table are 2000 records broken into
> around 50 different Measures with the top ten rates for different
organizations
> (OrgID).
>
> What I am trying to capture is for each Measure ID, I need the ranking for
OrgID
> 129. If OrgID 129 came in 3rd place for Measure ID 1, I need that. If OrgID
> 129 came in 10th place in measure 4, 7, 8 etc I need to capture that for a
graph
> that I will eventually build showing what place OrgID 129 placed in all of the
> Measures.
>
>
> Below if my first stab at the SQL to rank all of the OrgIDs by each Measure
ID.
> However, when I try to run it, I get an error saying a subquery can return
more
> than one filed without using the EXISTS reserved word in the main query's FROM
> clause.
>
> Where would I put the code to only count the ranking for OrgID 129?
>
> SELECT tblTopTenFinalResults.[Measure ID], tblTopTenFinalResults.Rate,
> tblTopTenFinalResults.OrgID, (Select Q.[Measure ID], Q.[OrgID], Q.[Rate],
> (SELECT COUNT(*) FROM
> [tblTopTenFinalResults] Q1
> WHERE Q1.[Measure ID] = Q.[Measure ID]
> AND Q1.[Rate] <Q.[Rate]) +1 AS Rank
> FROM tblTopTenFinalResults AS Q
> ORDER BY Q.[Measure ID], Q.Rate, Q.OrgID;) AS OrgRanking
> FROM tblTopTenFinalResults
> ORDER BY tblTopTenFinalResults.[Measure ID], tblTopTenFinalResults.Rate DESC;
>
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar