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

Kamis, 22 Desember 2011

Re: [MS_AccessPros] Getting top ten rates

I'll give the indexes a try. I forgot to add the first step to this process is I make a table of all active MeasureIDs then do my sorting. What about doing something to limit the table from the start? Here is the code I use to make the source table. I had to do this to sort out fields with NA for example and to make sure it is active. This query works fine and isn't too slow, its the ranking query that is slow. I tried an append query thinking maybe Access would read each MeasureID record, sort it, append it and go on thinking it would be faster but that didn't help.

SELECT [tblHEDIS2011-AllMeasures].MeasureID, [tblHEDIS2011-AllMeasures].Rate, [tblHEDIS2011-AllMeasures].ShortName, subtblHEDISMeasureID.ShortName, [tblHEDIS2011-AllMeasures].OrgID INTO tblTopTen
FROM subtblHEDISMeasureID LEFT JOIN [tblHEDIS2011-AllMeasures] ON subtblHEDISMeasureID.[Measure ID] = [tblHEDIS2011-AllMeasures].MeasureID
GROUP BY [tblHEDIS2011-AllMeasures].MeasureID, [tblHEDIS2011-AllMeasures].Rate, [tblHEDIS2011-AllMeasures].ShortName, subtblHEDISMeasureID.ShortName, [tblHEDIS2011-AllMeasures].OrgID, subtblHEDISMeasureID.ActiveMeasure, [tblHEDIS2011-AllMeasures].ReportingProduct
HAVING ((([tblHEDIS2011-AllMeasures].Rate) Not In ("NR","NA","ND","NB","EXC")) AND ((subtblHEDISMeasureID.ActiveMeasure)=True))
ORDER BY [tblHEDIS2011-AllMeasures].MeasureID, [tblHEDIS2011-AllMeasures].Rate DESC;

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