ACCESS FEED: RE: [MS_AccessPros] Re: Getting Top 10 Rates with multiple Measure IDs

Hi John

I'm glad you got it working :-)

I'm sure you can speed up the query significantly if you make both MeasureID
and Rate indexed (duplicates allowed) and add an AutoNumber primary key
named "ID" (or something else to your liking). With a primary key, you can
simplify the SQL somewhat:

SELECT M.MeasureID, M.Rate, M.Name, M.ShortName,
M.MeasureNameAbbreviation, M.MeasureName, M.ReportingProduct
FROM [tblHEDIS2010-AllMeasures] AS M
WHERE M.ID In (select top 10 X.ID
from [tblHEDIS2010-AllMeasures] as X
where (X.MeasureID=M.MeasureID)
and (IsNumeric(X.Rate)<>0)
and (X.ReportingProduct="HMO")
order by Val(X.Rate) desc)
ORDER BY M.MeasureID, M.Rate DESC;

Good luck!
Graham

> From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of jfakes.rm
> Sent: Thursday, 14 July 2011 02:27
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: Getting Top 10 Rates with multiple Measure
IDs
>
>
> Graham,
> That worked! It took about 15 minutes to run though :( however, there are
over 85k records to sort and process.
>
> I had to make a few changes to the code:
>
> SELECT M.MeasureID, M.Rate, M.Name, M.ShortName,
M.MeasureNameAbbreviation, M.MeasureName, M.ReportingProduct
> FROM [tblHEDIS2010-AllMeasures] AS M
> WHERE (((M.Rate) In (select top 10 X.Rate
> from [tblHEDIS2010-AllMeasures] as X
> where (X.MeasureID=M.MeasureID)
> and (IsNumeric(X.Rate)<>0)
> and (X.ReportingProduct="HMO")
> order by Val(X.Rate) desc)) AND ((M.ReportingProduct)="HMO"))
> ORDER BY M.MeasureID, M.Rate DESC;
>
> Sorry for the confusion.
>
> There are multiple measure IDs as you pointed out. The reason
subtblHEDISMeasureID was in the query was to make sure I only capture active
measures (this varies from year to year so I needed to set the basic query
up, then just change to the next years tables as soon as they are
available).
>
> I was finally able to figure out why the query wasn't running (space
issues).
>
> --- In MS_Access_Professionals@yahoogroups.com, "Graham Mandeno"
<graham@...> wrote:
> >
> > Hi John
> >
> > If [MeasureID] (with or without the space!) is the primary key on BOTH
> > tables, then there must be a one-to-one relationship - correct? If so,
then
> > why is it necessary to include subtblHEDISMeasureID in the query at all,
> > given that the only field you are including from that table is [Measure
ID]?
> >
> > Actually, the PK in tblHEDIS2010-AllMeasures *cannot* be [MeasureID],
> > otherwise there could be only ONE record for each [MeasureID] and the
whole
> > exercise of getting the top 10 would be meaningless. There is something
not
> > quite making sense in your description of the table structure, sorry!
> >
> > Assuming then that there are multiple records for each [MeasureID] and
that
> > [tblHEDIS2010-AllMeasures] has no PK, and that there is no requirement
to
> > include subtblHEDISMeasureID in the query, the following should work:
> >
> > SELECT M.MeasureID, M.Rate, M.Name, M.ShortName,
> > M.MeasureNameAbbreviation, M.MeasureName
> > FROM [tblHEDIS2010-AllMeasures] as M
> > WHERE (M.Rate in (select top 10 X.Rate
> > from [tblHEDIS2010-AllMeasures] as X
> > where (X.MeasureID=M.MeasureID)
> > and (IsNumeric(X.Rate)<>0)
> > and (X.ActiveMeasure<>0)
> > and (X.ReportingProduct="HMO")
> > order by Val(X.Rate) desc))
> > AND (X.ActiveMeasure<>0)
> > AND (X.ReportingProduct="HMO")
> > ORDER BY M.MeasureID, M.Rate desc;
> >
> > This could be less complex and more efficient if
tblHEDIS2010-AllMeasures
> > had a PK, because the filter on ActiveMeasure and ReportingProduct would
not
> > need to be repeated in the main query. It will also run faster if Rate
is
> > indexed.
> >
> > Good luck! :-)
> >
> > Graham
> >
> > PS: You said "I couldn't get your SQL to run". What was the error
message?
> >
> > > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of jfakes.rm
> > > Sent: Wednesday, 13 July 2011 01:58
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: Re: [MS_AccessPros] Getting Top 10 Rates with multiple
Measure
> > IDs
> > >
> > >
> > > Graham,
> > > My name is John Fakes.
> > >
> > > I couldn't get your SQL to run. I'm sure its because I don't know SQL
and
> > I just need to change something.
> > >
> > > The PK for subtblHEDISMeasureID is Measure ID. The PK for
> > tblHEDIS2010-AllMeasures is MeasureID. Its annoying that the vendor uses
a
> > space in one table and not in the other. I have at least 50 query's for
> > different reports/graphs in this database so changing the PK isn't
really an
> > option
> > >
> > > I decided to try and make this easy by creating a new table with only
the
> > basic data I need. I have pasted the SQL for what I used below. This is
> > returning the top 10 Measure ID's, not the top ten Rates for each
Measure
> > ID. Any suggestions?
> > >
> > > SELECT tblTopTen.[Measure ID], tblTopTen.Rate, tblTopTen.Name,
> > tblTopTen.ShortName, tblTopTen.MeasureNameAbbreviation,
> > tblTopTen.MeasureName
> > > FROM tblTopTen
> > > WHERE (((tblTopTen.Rate) In (Select Top 10 [tblTopTen].[Rate]
> > > from [tblTopTen]
> > > Order by [Rate] Desc)))
> > > ORDER BY tblTopTen.[Measure ID], tblTopTen.Rate DESC;
> > >
> > > When I run the below code, I get a mixture of Rates (not the top ten).
I
> > might get 2 rates for one Measure ID, then I might get 4 rates for
another
> > Measure ID. I got as many as 8 rates for one Measure ID even though I
know
> > there are more than 10 records per Measure ID.
> > >
> > > SELECT subtblHEDISMeasureID.[Measure ID],
[tblHEDIS2010-AllMeasures].Rate,
> > [tblHEDIS2010-AllMeasures].Name, [tblHEDIS2010-AllMeasures].ShortName,
> > [tblHEDIS2010-AllMeasures].MeasureNameAbbreviation,
> > [tblHEDIS2010-AllMeasures].MeasureName
> > > FROM subtblHEDISMeasureID LEFT JOIN [tblHEDIS2010-AllMeasures] ON
> > subtblHEDISMeasureID.[Measure ID] = [tblHEDIS2010-AllMeasures].MeasureID
> > > WHERE ((([tblHEDIS2010-AllMeasures].Rate) In (Select Top 10
> > [tblHEDIS2010-AllMeasures].[Rate]
> > > from [tblHEDIS2010-AllMeasures]
> > > Where [subtblHEDISMeasureID].[Measure ID] =
> > > [tblHEDIS2010-AllMeasures].[MeasureID]
> > > and [tblHEDIS2010-AllMeasures].[Rate] Not In ("NR","NA","ND","NB",
"EXC")
> > > Order by [Rate] Desc)) AND ((subtblHEDISMeasureID.ActiveMeasure)=True)
AND
> > (([tblHEDIS2010-AllMeasures].ReportingProduct)="HMO"))
> > > ORDER BY subtblHEDISMeasureID.[Measure ID],
> > [tblHEDIS2010-AllMeasures].Rate DESC;
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com, "Graham Mandeno"
> > <graham@> wrote:
> > > >
> > > > Hi jfakes
> > > >
> > > > (What is your name, by the way? We like to be friendly around here
:-)
> > > >
> > > > As far as I can tell, there are several problems with your SQL:
> > > > 1. As has already been pointed out, you don't need a GROUP BY clause
> > > > 2. Your subquery is returning the top 10 *Rates* not the IDs of the
> > > > *records* with the top 10 rates.
> > > > 3. If Rate is a text field then you will get a text sort, which is
not
> > > > necessarily in numerical order (1, 10, 100, 11, 2, 3, etc)
> > > >
> > > > Does your [tblHEDIS2010-AllMeasures] table have a primary key? And
how
> > is
> > > > it related to [subtblHEDISMeasureID]?
> > > >
> > > > Assuming [Measure ID] is the primary key of [subtblHEDISMeasureID],
and
> > > > there is a one-to-many relationship with [tblHEDIS2010-AllMeasures]
on
> > > > [Measure ID]<[MeasureID], and [tblHEDIS2010-AllMeasures] has a
primary
> > key
> > > > named [ID], you could use something like this:
> > > >
> > > > SELECT M.[Measure ID], A.Rate, A.Name, A.ShortName,
> > > > A.MeasureNameAbbreviation, A.MeasureName
> > > > FROM [subtblHEDISMeasureID] as M LEFT JOIN
[tblHEDIS2010-AllMeasures] as
> > A
> > > > ON M.[Measure ID] = A.[MeasureID]
> > > > WHERE (M.ActiveMeasure<>0)
> > > > AND (A.[ID] in (select top 10 [ID] from [tblHEDIS2010-AllMeasures]
as X
> > > > where X.[MeasureID]=M.[Measure ID] AND IsNumeric(X.Rate)<>0 AND
> > > > X.ReportingProduct="HMO" order by Val(X.Rate) desc))
> > > > ORDER BY M.[Measure ID], A.Rate desc;
> > > >
> > > > Note the use of IsNumeric to eliminate text values and the use of
Val to
> > > > sort on numeric, not text values.
> > > >
> > > > Good luck! :-)
> > > >
> > > > -- Graham Mandeno
> > > >
> > > > > From: MS_Access_Professionals@yahoogroups.com
> > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
jfakes.rm
> > > > > Sent: Tuesday, 12 July 2011 02:42
> > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > Subject: Re: [MS_AccessPros] Getting Top 10 Rates with multiple
> > Measure
> > > > IDs
> > > > >
> > > > >
> > > > > John V. and Duane,
> > > > > I used the cleaned up SQL code. Thanks for your help.
> > > > >
> > > > > However, the problem remains. I have checked and I know for a fact
> > there
> > > > are more than 10 rates for every measure. The way the vendor sends
the
> > > > table, the rate is text, and there are several rates that are not
> > numbers
> > > > hence the need for the following exclusions:
> > > > [tblHEDIS2010-AllMeasures].[Rate] Not In
("NR","NA","ND","NB","EXC").
> > > > >
> > > > > When I run the code (see below) I still don't get the top 10 rates
for
> > > > each measure. I'm wondering if the query hits one of the exclusions
and
> > > > stops?
> > > > >
> > > > > Here is the code I am using:
> > > > > SELECT subtblHEDISMeasureID.[Measure ID],
> > [tblHEDIS2010-AllMeasures].Rate,
> > > > [tblHEDIS2010-AllMeasures].Name,
[tblHEDIS2010-AllMeasures].ShortName,
> > > > [tblHEDIS2010-AllMeasures].MeasureNameAbbreviation,
> > > > [tblHEDIS2010-AllMeasures].MeasureName
> > > > > FROM subtblHEDISMeasureID LEFT JOIN [tblHEDIS2010-AllMeasures] ON
> > > > subtblHEDISMeasureID.[Measure ID] =
[tblHEDIS2010-AllMeasures].MeasureID
> > > > > GROUP BY subtblHEDISMeasureID.[Measure ID],
> > > > [tblHEDIS2010-AllMeasures].Rate, [tblHEDIS2010-AllMeasures].Name,
> > > > [tblHEDIS2010-AllMeasures].ShortName,
> > > > [tblHEDIS2010-AllMeasures].MeasureNameAbbreviation,
> > > > [tblHEDIS2010-AllMeasures].MeasureName,
> > subtblHEDISMeasureID.ActiveMeasure,
> > > > [tblHEDIS2010-AllMeasures].ReportingProduct
> > > > > HAVING ((([tblHEDIS2010-AllMeasures].Rate) In (Select Top 10
> > > > [tblHEDIS2010-AllMeasures].[Rate] from [tblHEDIS2010-AllMeasures]
> > > > > Where [subtblHEDISMeasureID].[Measure ID] =
> > > > [tblHEDIS2010-AllMeasures].[MeasureID]
> > > > > and [tblHEDIS2010-AllMeasures].[Rate] Not In ("NR","NA","
ND","NB",
> > "EXC")
> > > > > Order by [Rate] Desc)) AND
((subtblHEDISMeasureID.ActiveMeasure)=True)
> > AND
> > > > (([tblHEDIS2010-AllMeasures].ReportingProduct)="HMO"))
> > > > > ORDER BY subtblHEDISMeasureID.[Measure ID],
> > > > [tblHEDIS2010-AllMeasures].Rate DESC;
> > > > >
> > > > > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas"
<john@>
> > > > wrote:
> > > > > >
> > > > > > jfakes-
> > > > > >
> > > > > > No need for a Totals query, but otherwise your SQL look correct.
If
> > > > there are
> > > > > > only 2 rates or 7 rates in [tblHEDIS2010-AllMeasures] for a
given
> > > > Measure ID,
> > > > > > then that's all you'll get.
> > > > > >
> > > > > > 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/
> > > > > > (Paris, France)
> > > > > >
> > > > > >
> > > > > >
> > > > > > -----Original Message-----
> > > > > > From: MS_Access_Professionals@yahoogroups.com
> > > > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
> > jfakes.rm
> > > > > > Sent: Friday, July 08, 2011 6:56 PM
> > > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > > Subject: [MS_AccessPros] Getting Top 10 Rates with multiple
Measure
> > IDs
> > > > > >
> > > > > > I need to determine the top 10 rates for each measure ID. There
are
> > over
> > > > 100
> > > > > > measure ID's and I have finally came up with the below listed
SQL,
> > > > however, it
> > > > > > arbitrarily displays anywhere from two rates per measure id up
to 7
> > > > rates for
> > > > > > other measure ids. Any idea how I can fix this issue? I think it
> > might
> > > > have to
> > > > > > do with my order by, however, I haven't been able to figure out
how
> > to
> > > > get the
> > > > > > top 10 rates for each measure id.
> > > > > >
> > > > > > And no, I can't change the tables as it comes from an outside
vendor
> > and
> > > > that is
> > > > > > how I get the data.
> > > > > >
> > > > > > Thanks.
> > > > > >
> > > > > > SELECT subtblHEDISMeasureID.[Measure ID],
> > > > [tblHEDIS2010-AllMeasures].Rate,
> > > > > > [tblHEDIS2010-AllMeasures].Name,
> > [tblHEDIS2010-AllMeasures].ShortName,
> > > > > > [tblHEDIS2010-AllMeasures].MeasureNameAbbreviation,
> > > > > > [tblHEDIS2010-AllMeasures].MeasureName
> > > > > > FROM subtblHEDISMeasureID LEFT JOIN [tblHEDIS2010-AllMeasures]
ON
> > > > > > subtblHEDISMeasureID.[Measure ID] =
> > [tblHEDIS2010-AllMeasures].MeasureID
> > > > > > GROUP BY subtblHEDISMeasureID.ActiveMeasure,
> > > > subtblHEDISMeasureID.[Measure ID],
> > > > > > [tblHEDIS2010-AllMeasures].Rate,
[tblHEDIS2010-AllMeasures].Name,
> > > > > > [tblHEDIS2010-AllMeasures].ShortName,
> > > > > > [tblHEDIS2010-AllMeasures].MeasureNameAbbreviation,
> > > > > > [tblHEDIS2010-AllMeasures].MeasureName,
> > > > > > [tblHEDIS2010-AllMeasures].ReportingProduct
> > > > > > HAVING (((subtblHEDISMeasureID.ActiveMeasure)=True) AND
> > > > > > (([tblHEDIS2010-AllMeasures].Rate) In (Select Top 10
> > > > > > [tblHEDIS2010-AllMeasures].[Rate] from
[tblHEDIS2010-AllMeasures]
> > Where
> > > > > > [subtblHEDISMeasureID].[Measure ID] =
> > > > [tblHEDIS2010-AllMeasures].[MeasureID] and
> > > > > > [tblHEDIS2010-AllMeasures].[Rate] Not In
("NR","NA","ND","NB","EXC")
> > > > Order by
> > > > > > [Rate] Desc)) AND
> > (([tblHEDIS2010-AllMeasures].ReportingProduct)="HMO"))
> > > > > > ORDER BY subtblHEDISMeasureID.[Measure ID],
> > > > [tblHEDIS2010-AllMeasures].Rate
> > > > > > DESC;
> > > >
> > >
> > >
> >
>
>