Show top 10 count for data

[Guest]

I am trying to create a crosstab query in which the row is supposed to show
the top 10 counts for a particular field. Any suggestions?

 

[Duane Hookom]

My suggest is that you try to be less vague with your question. Could you
provide some sample records and how you would expect them to display in your
crosstab? Also, what have you found out so far other than you assume you
need a crosstab query?

 

[Guest]

Sorry about that. Let me try to clarify.

Let's say that I have a table with the fields, 'Diagnosis_Code',
'Diagnosis_Name' and 'Procedure_Codes' and another table with the field,
'ICD9_Code'. ICD9_Code and Diagnosis_Code are related.

I would like to run a crosstab query that the join b/t ICD9_Code and
Diagnosis_Code to disaplay the corresponding 5 Diagnosis_Names and the count
of the patients with those that occur with the greatest frequencies.

This is why I wanted Diagnosis_Name to be a row heading, the corresponding
Procedure_Code as the colum heading the and the count of the patients that
have the top 5 Diagnosis_Name.

Hope that helps. Thanks for helping me out.

My suggest is that you try to be less vague with your question. Could you
provide some sample records and how you would expect them to display in your
crosstab? Also, what have you found out so far other than you assume you
need a crosstab query?

 

[Duane Hookom]

To get the top 5 Diagnosis_Name create a query from your table(s). Group By
and Count Diagnosis_Name, sort descending by the count, and set the TOP
predicate to 5. This will return the 5 most popular Diagnosis_Name that can
be used in another query.

--
Duane Hookom
MS Access MVP

Sorry about that. Let me try to clarify.

Let's say that I have a table with the fields, 'Diagnosis_Code',
'Diagnosis_Name' and 'Procedure_Codes' and another table with the field,
'ICD9_Code'. ICD9_Code and Diagnosis_Code are related.

I would like to run a crosstab query that the join b/t ICD9_Code and
Diagnosis_Code to disaplay the corresponding 5 Diagnosis_Names and the
count
of the patients with those that occur with the greatest frequencies.

This is why I wanted Diagnosis_Name to be a row heading, the corresponding
Procedure_Code as the colum heading the and the count of the patients that
have the top 5 Diagnosis_Name.

Hope that helps. Thanks for helping me out.

 

[Guest]

Thank you. That was very helpful.

To get the top 5 Diagnosis_Name create a query from your table(s). Group By
and Count Diagnosis_Name, sort descending by the count, and set the TOP
predicate to 5. This will return the 5 most popular Diagnosis_Name that can
be used in another query.