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
>
Tidak ada komentar:
Posting Komentar