Senin, 25 Juli 2011

[MS_AccessPros] Re: Ranking multiple Rates

 

John
Auuuuuuugh! That was so simple, however, not knowing SQL I didn't see it. Guess I need to buy your SQL Queries for Mere Mortals book.

I had intended on going more into VBA, but maybe SQL would be better.

Thanks so much.
John

--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> 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
>

__._,_.___
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar