Selasa, 12 Juli 2011

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

__._,_.___
Recent Activity:
MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

.

__,_._,___

Tidak ada komentar:

Posting Komentar