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;
Tidak ada komentar:
Posting Komentar