ACCESS FEED: RE: [MS_AccessPros] Getting top ten rates

Kamis, 22 Desember 2011

RE: [MS_AccessPros] Getting top ten rates

John F.-

There's really not a faster way to do it. It might help if you have indexes on
MeasureID and Rate.

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/
(Waco, Texas <yee haw>)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of jfakes.rm
Sent: Thursday, December 22, 2011 9:34 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Getting top ten rates

Here is an overview of what I am doing. I have a table with around 73,000
records in it. There are around 200 different MeasureID's with an associated
Rate. I have to get the top ten Rates for each MeasureID. Each MeasureID has
around 300 records associated with it.

With the help of several people in this forum (John V and Bob H come to mind)I
was able to get the sorting/ranking to work. The problem is, it takes more than
30 minutes to run the query. Is there a way to modify the below query to speed
the process up? I thought about doing some kind of sort to get the records for
each MeasureID down to maybe 30 records then do the ranking but I'm not sure how
to build the initial sort.

SELECT M.MeasureID, M.Rate, M.subtblHEDISMeasureID_ShortName, M.OrgID INTO
tblTopTenFinalResults
FROM tblTopTen AS M
WHERE (((M.Rate) In (select top 10 X.Rate
from [tblTopTen] as X
where (X.[MeasureID]=M.[MeasureID])
and (IsNumeric(X.Rate)<>0)
order by Val(X.Rate) desc)))
ORDER BY M.MeasureID, M.Rate DESC;

Thanks, John F.

------------------------------------

Yahoo! Groups Links