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

 

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;
> >
>
>

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (8)

Recent Activity:

• New Members 9
• New Files 6

Visit Your Group

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___