Top 3 query gives 4 records

[Guest]

I have a query to report the top 3 drugs used per client. My SQL is:

SELECT Clients.ClientName, OverallDrugsMonthly.[Drug Name],
OverallDrugsMonthly.[Net Rxs]
FROM OverallDrugsMonthly INNER JOIN Clients ON OverallDrugsMonthly.Client =
Clients.ClientID
WHERE (((OverallDrugsMonthly.[Drug Name]) In (Select Top 3 [Drug Name] From
OverallDrugsMonthly Where Client = Clients.ClientID Order By [Total Rx Users]
Desc)))
ORDER BY Clients.ClientName, OverallDrugsMonthly.[Net Rxs] DESC;

It works fine unless the number of Net Rxs is the same- ie if the net Rxs
for a specific client returns as 102, 46, 46, and 32 it reports all four
drugs instead of just the drugs associated with the Net Rxs count of 102, 46,
46. How do I make it stop reporting the top 4 rankings if 2 of the rankings
are the same?

Thanks for your help.

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "TopQuery.mdb" which illustrates how to do this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Michel Walsh]

Hi,

You can ORDER BY the primary key, in the end:

ORDER BY Clients.ClientName, OverallDrugsMonthly.[Net Rxs] DESC,
PrimaryKeyField


If you don't have a PK, you can use

ORDER BY Clients.ClientName, OverallDrugsMonthly.[Net Rxs] DESC, Rnd(
ANumericalFieldnameHere ) ;




which computes a random value for each record, and thus, can be use as
tie-breaker.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thank you. That works.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "TopQuery.mdb" which illustrates how to do this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I have a query to report the top 3 drugs used per client. My SQL is:

SELECT Clients.ClientName, OverallDrugsMonthly.[Drug Name],
OverallDrugsMonthly.[Net Rxs]
FROM OverallDrugsMonthly INNER JOIN Clients ON OverallDrugsMonthly.Client =
Clients.ClientID
WHERE (((OverallDrugsMonthly.[Drug Name]) In (Select Top 3 [Drug Name] From
OverallDrugsMonthly Where Client = Clients.ClientID Order By [Total Rx Users]
Desc)))
ORDER BY Clients.ClientName, OverallDrugsMonthly.[Net Rxs] DESC;

It works fine unless the number of Net Rxs is the same- ie if the net Rxs
for a specific client returns as 102, 46, 46, and 32 it reports all four
drugs instead of just the drugs associated with the Net Rxs count of 102, 46,
46. How do I make it stop reporting the top 4 rankings if 2 of the rankings
are the same?

Thanks for your help.

 

[Guest]

That's another option that works, too. Thanks for the help.

Hi,

You can ORDER BY the primary key, in the end:

ORDER BY Clients.ClientName, OverallDrugsMonthly.[Net Rxs] DESC,
PrimaryKeyField


If you don't have a PK, you can use

ORDER BY Clients.ClientName, OverallDrugsMonthly.[Net Rxs] DESC, Rnd(
ANumericalFieldnameHere ) ;




which computes a random value for each record, and thus, can be use as
tie-breaker.



Hoping it may help,
Vanderghast, Access MVP

I have a query to report the top 3 drugs used per client. My SQL is:

SELECT Clients.ClientName, OverallDrugsMonthly.[Drug Name],
OverallDrugsMonthly.[Net Rxs]
FROM OverallDrugsMonthly INNER JOIN Clients ON OverallDrugsMonthly.Client
=
Clients.ClientID
WHERE (((OverallDrugsMonthly.[Drug Name]) In (Select Top 3 [Drug Name]
From
OverallDrugsMonthly Where Client = Clients.ClientID Order By [Total Rx
Users]
Desc)))
ORDER BY Clients.ClientName, OverallDrugsMonthly.[Net Rxs] DESC;

It works fine unless the number of Net Rxs is the same- ie if the net Rxs
for a specific client returns as 102, 46, 46, and 32 it reports all four
drugs instead of just the drugs associated with the Net Rxs count of 102,
46,
46. How do I make it stop reporting the top 4 rankings if 2 of the
rankings
are the same?

Thanks for your help.