Top 5 by specialty in subquery

C

Chuck W

Hi,
I have a table called tblTop5DRG_BySpecialty which has the following fields:
Specialty (Cardiology, Internal Medicine etc.)
DRG (A number which is a type of diagnosis)
DRG title
DRGCountSpec (the number of times a DRG occured)

I want to create a query that will give me the top five DRGs by Specialty.
I created the following query but it is not working. It just gives me the
results of the table which is all DRGs by specialty. Can someone help? Chuck

SELECT tblTop5DRG_BySpecialty.Specialty, tblTop5DRG_BySpecialty.DRG,
tblTop5DRG_BySpecialty.[DRG title], tblTop5DRG_BySpecialty.DRGCountSpec INTO
tblTop5DRG_BySpecialty2
FROM tblTop5DRG_BySpecialty
WHERE (((tblTop5DRG_BySpecialty.DRG) In (SELECT TOP 5 DRG FROM
tblTop5DRG_BySpecialty AS Dupe WHERE Dupe.Specialty =
tblTop5DRG_BySpecialty.Specialty ORDER BY Dupe.DRGCountSpec DESC)));
 
C

Chuck W

Sorry my error. It is giving me ties which is why there is more than five
for many.
Chuck
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

select distinctrow using date on form 12
Returning 0 for null Criteria Value 3
Subquery Syntax error 1
Show select table entries based on yes/no checkbox answers 1
top items in a pivot by page 0
Subquery to get Top 5... 2
Subquery Question - continued 0
Top 5 by group 0

Top