Re: display top 5 records of each subgroup in a query


SELECT T.*
FROM TheTable as T
WHERE T.[Brand Name] IN
(SELECT TOP 5 T2.[Brand Name]
FROM TheTable as T2
WHERE T2.[General Therapeutic Category] =
T.[General Therapeutic Category]
ORDER BY [# of Rx's] Descending)

Simon L wrote:

I want to set up a separate query to display the top 5 records of each
subgroup. The following are the fields and sample data from another query.
What I want is a query that will display the top 5 Brand Name drugs in
Allergy, in Antiemesis, and in Asthma based on the # of Rxs. Any help will
be greatly appreciated. Thanks

General Therapeutic Category Brand Name # of RX's
ALLERGY ALLEGRA 65
ALLERGY ALLEGRA-D 12 HOUR 41
ALLERGY NASACORT AQ 29
ALLERGY ALAVERT 24
ALLERGY ZYRTEC
23
ALLERGY FLUTICASONE PROPIONATE 20
ALLERGY ZYRTEC-D 10
ALLERGY LORATADINE 10
ALLERGY HYDROXYZINE HCL 7
ALLERGY PROMETHAZINE HCL 7
ALLERGY FEXOFENADINE HCL 5
ANTIEMESIS/ANTIVERTIGO MECLIZINE HCL 31
ANTIEMESIS/ANTIVERTIGO TRANSDERM-SCOP 5
ANTIEMESIS/ANTIVERTIGO PROMETHAZINE HCL 1
ANTIEMESIS/ANTIVERTIGO PROCHLORPERAZINE MALEATE 1
ASTHMA ADVAIR DISKUS 76
ASTHMA ALBUTEROL 56
ASTHMA SINGULAIR 17
ASTHMA COMBIVENT 16
ASTHMA ALBUTEROL SULFATE 15
ASTHMA FLOVENT HFA 9
ASTHMA ATROVENT HFA 9
.

Relevant Pages

  • Re: display top 5 records of each subgroup in a query
    ... The subquery is going to be called once for each record in main query. ... Simon L wrote: ... I want to set up a separate query to display the top 5 records of each ... ANTIEMESIS/ANTIVERTIGO PROCHLORPERAZINE MALEATE 1 ...
    (microsoft.public.access.queries)
  • Re: DB Result Wizard Error
    ... Also noted that one folder, which contains no information can't be deleted, ... >> For instance Equipment Serial Number, Equipment Model Number, Cost Accounting ... >> to display the new equipment information. ... >> is in the table was added to the query. ...
    (microsoft.public.frontpage.programming)
  • RE: Just starting to design a form (or is it a query?)
    ... A Form is used to display the data and allow ... A query can be the record source for a form. ... that match an ID in the Assessment table. ... Now, assuming this is the case, what you need is a form with a sub form. ...
    (microsoft.public.access.gettingstarted)
  • Re: database lookup
    ... the query on the subsequent page, and display the "drill down" data. ... The thing with PHP and MySQL is that you need the user to do an action ... I tried to state that I have the database designed and implemented but I ...
    (alt.php)
  • Re: how to show two rows in an unbound continuous form
    ... Thanks a lot for your prompt response.Actually i thought of using crosstab ... I have created a crosstab query on the table i gave you earlier.The first ... have bound the form's controlsource also,of course). ... clue how to display the second row to show another leave period(01-05-2009 to ...
    (microsoft.public.access.formscoding)