Data with 65000 lines sorted by number used most often

[Jeanie]

I have a query I have run in a database program for claims data. The query has 65000 lines. Each line is for a medical claim tied to a patient's ID number. Each ID number could have any number of lines, depending on the number of claims. I would like to sort the data in ACCESS (maybe?) with the ID numbers with the most number of lines showing first. I do not want to hide any lines. So, for example, if patient 11111 has 20 lines and patient 22222 has 25 lines and patient 33333 has 1 line I want all the lines for patient 22222 to be listed first, then all the lines for 11111 then all the lines for 33333.

I do not know a whole lot about ACCESS- I can do simple queries, forms,and reports...but do not know how to do the advanced stuff (I will be taking a class!).

Any help would be appreciated.

Thanks,

Jeanie

 

[PaulBricker]

Well you can do this with 2 queries fairly easily. First, create a query and add the PatientID twice. On the menu bar go to View..Totals and turn on the Totals. Group By the first PatientID and Count the second PatientID. Save this query. Then in a new query, add your Table and the Query1. Join them on PatientID. Add all the Fields you want from the table and add the CountofPatientID from Query1. Set the Sort for CountofPatientID to Decending. You can choose to show this field or not by checking or unchecking the Show box. This should do what you need.
Post back with any problems.

Paul

 

[Jeanie]

Thanks for the advice...I'm not going to be able to try it however, because we discovered that we need to do something entirely different!

Will keep this suggestion, however, for future use.

Thx,

Jeanie

 

[PaulBricker]

No problem. Good luck.

Paul