Top 5 Records by Max Count

I have a table that counts the number of hospital visits by hospital and doctor. Since there are hundreds of doctors per hospital, I'd like to limit the return of doctors to the top 5 based on descending highest number of visits. It would look like:

Hospital 1 Provider 1 500 visits
Hospital 1 Provider 2 300 visits
Hospital 1 Provider 3 250 visits
Hospital 1 Provider 4 200 visits
Hospital 1 Provider 5 100 visits
Hospital 2 Provider 1 1200 Visits
Hospital 2 Provider 2 800 visits etc.

How would I code the number of visits to exclude any but the top 5 records? I already have the sum of visits at the provider level... Hope this makes sense... Thanks so much! PS, not too savvy at SQL yet--I use query design view... I know I know! lol

Why not sort by descending order of visits in query design?

I would if there were only 5 doctors per hospital, but there are hundreds. I'm looking limit the result to the top 5 docs per hospital.

Top 5 docs based on number of visits.

Perhaps this article will give you some pointers.

I was interested in your post and decided to try to create a query to satisfy your parameters. You didn't say what version of Access you have, nor did you show a table layout. So, I created a table and added some records. I created a query ( after several attempts) that seems to satisfy the criteria.

I have attached an mdb ( I have Acc2003) with a Table, 2 queries and a startup form.
The form will show the sample data in my table tblHospitalVisits. Also, you can run the query to show the Top 5 Provider Visits per Hospital.

You can see the sql underlying the query by looking at the sql view. Similarly you can see my table design/structure by looking at the table design.

You will see I included an autonumber field to identify individual records. The autonumber field is key to the query. You could renumber the records and still get the same result.
hope it helps.
Good luck.