Short of limiting the records completely in the first query, no. Loading into a
table with indexes will make a world of difference.
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:39 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Getting top ten rates
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
>
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar