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

I don't know why you are using a GROUP BY when there are no aggregates. I would change the SQL to simplify it to:
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;

It seems you are sorting on Rate which is a text field. Do you understand that "2" will sort after "10" in this query? Also, there is a space in front of " ND". Is this intentional?

I would think the "AND subtblHEDISMeasureID.ActiveMeasure=True" should be in the subquery. I'm not sure why you even need subtblHEDISMeasureID in the main query.

Duane Hookom
MS Access MVP

To: MS_Access_Professionals@yahoogroups.com
From: jfakes@rocketmail.com
Date: Mon, 11 Jul 2011 14:42:29 +0000
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;
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/