I don't run this query very often so I don't mind adding the indexes. Do you know of an efficient way to limit the make table query to a manageable number of records (say 30) instead of the 300+ now? Taking into account, I have to keep the top ten rates for each MeasureID.
--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> 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