Top ten values in a report

[medirate]

Can someone tell me how to get the top values in a report?



I need a report that shows the top ten doctors that a Patient has seen.
Determining the top ten is based on the value of a text field called Codes
located in a select query called NewPatients:



Doctor Patient Code

Dr. No Smith, S 99205

Dr. No Adams, G 99205

Dr. No Bong , W 99212

Dr. No Drake, P 99202

Dr. No Smith, S 99232

Dr. Treat Bong, W 99204

Dr. Treat Smith, S 99201

Dr. Treat Flem, A 99202

Dr. Wise Rice, V 99203

Dr. Wise Fell, M 99201

Dr. Wise Grant, P 99217



I need a report that adds up the number of times each patient has seen a
Doctor, but only if the codes has a value between 99201 and 99205. I suppose
using something like:

DCount("fldPhysician","NewPatients","fldCptCode Between '99201'And '99205')



But this returns a count of all the underlying records. I just need the top
ten grouped by doctor:



Dr. No: 3 codes

Dr. Treat: 3 codes

Dr. Wise: 2 codes, etc...



#1: How can I get a list of the top ten doctors grouped by the value in
Code?



#2 How can I get a list of the top ten doctors seen by unique Patient? For
example:



Dr. No: 4 unique patients

Dr. Treat: 3 unique patients

Dr. Wise: 3 unique patients, etc....





Thanks!

 

[Duane Hookom]

If you only want the totals, you should be able to create a totals query like:

SELECT TOP 10 fldPhysician, Count(fldCptCode) as NumOf
FROM NewPatients
WHERE fldCptCode Between "99201" And "99205"
GROUP BY fldPhysician
ORDER BY Count(fldCptCode);

This assumes fldCptCode is text.

You should be able to do something similar for #2.