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