Top 2 but grouped

[UHNSTrust]

I have had a look around the forum for this and there are lots of examples but I am struggling to understand them. I am hoping that if I use my own data then I will be able to understand it better.

I have a table called 'tbl_Drugs_Prescribed'. This table contains 15 fields of which I only need two ('costcentre' and 'drug_name'). I want to be able to show the top 2 drugs prescribed (a simple count of the times a drug name appears) by cost centre.

So I have

costcentre drug_name
RJD DrugA
RJD DrugA
RJD DrugA
RJD DrugB
RJD DrugB
RJD DrugC
CCH DrugB
CCH DrugB
CCH DrugA
CCH DrugC
CCH DrugC
CCH DrugC

I want to be able to show

costcentre drug_name Count
RJD DrugA 3
RJD DrugB 2
CCH DrugC 3
CCH DrugB 2

I could do this in VBA but that would be slower. Can somebody show me how to do this efficiently through a query?

Thanks in advance
Jonathan

 

[UHNSTrust]

I have worked this out (I think!). However I would appreciate any advice on speeding it up as it is taking 15 minutes to run.

my query;

SELECT CCC.costcentre, CCC.drug_name, CCC.NoPrescribed
FROM sel_CountsByCC AS CCC
WHERE (((CCC.NoPrescribed) In (SELECT TOP 30 NoPrescribed FROM sel_CountsByCC WHERE costcentre=CCC.costcentre ORDER BY 1 DESC)));

Thanks if you had spent any time looking at this.

 

[dhookom]

Are we supposed to know the SQL of "sel_CountsByCC"?
Why TOP 30?

Duane
Hook'D on Access
MS Access MVP