John-
AH! A Make Table query builds no indexes! Build the table first with indexes
and use Delete / Insert queries to populate it.
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 10:11 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: 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
>
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar