This should be a little more readable version of the original SQL:
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;
Duane Hookom
MS Access MVP
To: MS_Access_Professionals@yahoogroups.com
From: jfakes@rocketmail.com
Date: Fri, 8 Jul 2011 16:56:14 +0000
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;
[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/
Tidak ada komentar:
Posting Komentar