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

Senin, 11 Juli 2011

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;
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>