Order By Count

[rcollins]

I know how to make the count of records in reports, but what I need to do is order them By value. I have many drugs that I group by, under which I have each client that uses the drug. I have a count(CName) which is what I want to order by. Waht I need out of this report is the top 10 medications. Thanks for helping

[Stewart Ross]

Hi. Don't do the counting in a report; do it in a query instead. You will find this a far more flexible approach. I say this because your report section totals cannot be used for sorting in the report's sorting and grouping settings. Instead, you need an appropriate field value from your recordsource query for the report's Sorting and Grouping.

To prepare a summary query from a detailed one use the Access query editor to add your detail table or query to the grid, select View, Totals to turn on the Group By settings for each field, insert the fields you want to group by, and add copies of fields you want to count, giving them new names reflecting the fact that they are counts or whatever. Then you can order your query by the count values.

After all this, if you View, SQL View in the query editor you can then change the SELECT ... FROM to SELECT TOP 10 ... FROM in the SQL view to show you only the top 10 results.

See how you get on and let us know.

-Stewart

[rcollins]

I can not get this to work right. I have the count, and that works, but I cant get the top 10 to work. Here is what I have right at the moment

SELECT DISTINCTROW qryMedications.RecordKey, qryMedications.ClientID, qryMedications.CName, qryMedications.MedicationCode, qryMedications.DrugName, qryMedications.OrderInReports, qryMedications.Strength, qryMedications.StartDate, qryMedications.EndDate, qryMedications.PrescriptionNumber, qryMedications.UserUpdate, qryMedications.DateUpdate, qryMedications.Type, First(qryMedications.Instructions) AS [First Of Instructions], Count(*) AS [Count Of qryMedications]
FROM qryMedications
GROUP BY qryMedications.RecordKey, qryMedications.ClientID, qryMedications.CName, qryMedications.MedicationCode, qryMedications.DrugName, qryMedications.OrderInReports, qryMedications.Strength, qryMedications.StartDate, qryMedications.EndDate, qryMedications.PrescriptionNumber, qryMedications.UserUpdate, qryMedications.DateUpdate, qryMedications.Type
ORDER BY Count(*) DESC;

When I try to add in the TOP 10 it still gives me all of the records

[Stewart Ross]

Hi. Not sure why you find that Top 10 returns all records - it should not, and does not when I've tested it. From the Access query designer select View, SQL View then replace the keyword DISTINCTROW with TOP 10, as shown below:

SELECT TOP 10 qryMedications.RecordKey, qryMedications.ClientID, ...
FROM qryMedications
GROUP BY qryMedications.RecordKey, qryMedications.ClientID,
...
ORDER BY Count(*) DESC;

Applying the same principle to a count of course applications (very different data, but the same sort of selection and count query as yours is) yields the results shown below, the first of which is only some of the rows for brevity, and the second uses Top 10 instead of DISTINCT:

Course	Block	Occurrence	App Count
INN560008	1	A	249
INN340001	1	A	225
INB330001	1	A	224
INN130202	1	A	189
INN570005	1	A	152
INN560009	1	A	134
INN550001	1	A	132
INN430103	1	A	126
INN540002	1	A	123
INN550004	1	A	112
UL530	F1	IB	108
U013X	F1	IB	103
U113B	F1	IB	92
U16XC	F1	IB	88
INN320010	1	A	82
INN410012	1	A	81
INN420008	1	A	80
...

Course	Block	Occurrence	App Count
INN560008	1	A	249
INN340001	1	A	225
INB330001	1	A	224
INN130202	1	A	189
INN570005	1	A	152
INN560009	1	A	134
INN550001	1	A	132
INN430103	1	A	126
INN540002	1	A	123
INN550004	1	A	112

-Stewart