Query repeating ID numbers

[JulieAsking]

I have a database of surgical operations. I wish to discover which patients over 2002 had the most surgical procedures done on them - say the top 10 patients.

I think this query will have to be worked on the autonumber Patient ID number as it is the only column that shows repeats. eg. Patient ID 7 shows up three times in the Patient ID column so I know the patient represented by the number 7 has had three separate operations done.

A Top Query would not work in this instance, and I need to get Access to tell me which Patient ID numbers have been repeated for the year 2002, then tell me the 10 that have had the most repeats. I sound cumbersome but is this possible please?

Thank you - Julie

 

[shay]

A happy new year to you!

Julie

Try this:

SELECT TOP 10 Count(tblPatients.PatientId) AS CountOfPatientId, tblPatients.PatientId
FROM tblPatients
GROUP BY tblPatients.PatientId
ORDER BY Count(tblPatients.PatientId) DESC;

hth

shay

 

[JulieAsking]

Thank you for replying Shay.

This is what I did firstly:

I changed your code to read exactly as my table/field names read.

I then copied and pasted the code into the Criteria area of the grid in the column including the Field of PtID and the Table of TPtDetails.

I got a message:The synax of the subquery in this expression is incorrect. Check the subquery's syntax and enclose the subquery in parentheses.

I have never done this stuff before and am not sure I placed the code in the right place.

This is what I did secondly:

I went to the grid and pulled down the Field called PtID. I went to SQL view, deleted what was already there (Access told me there were characters after the semi colon and if I left that code in place as well as your code I got a "Syntax error in FROM clause". If I deleted the code already there (SELECT TPtDetails.PtID
FROM TPtDetails) your code would run a query which came up as 2 columns named CountOfPtID and PtID. My PtIDs were all there but the CountofPtID column repeated the number 1 all the way down.

It seems this second method almost took me there. Are you able to understand the garble and put me right? Is there a way to do this query in the grid?

Thanks again for any help. Happy New Year to you also.

Julie

 

[JulieAsking]

In case it helps anyone who is not familiar with SQL I got this done using the grid. I dragged firstly the name field to the grid, then the Patient Id field. I clicked on the Total button in the toolbar which inserted 'Group By' in the Total cells. I left this stand under the Name field but in the Patient ID field I changed it to Count. This ran the query and all I had to do was sort the patient ID field in descending order to get what I wanted.

I have posted the SQL in case it might help anyone else.

SELECT TPtDetails.LastName, Count(TPtDetails.PtID) AS CountOfPtID
FROM TPtDetails INNER JOIN TOpDetails ON TPtDetails.PtID = TOpDetails.PtID
GROUP BY TPtDetails.LastName;


Julie

 

[shay]

Hi

Sorry I wasn't able to get back to you. We've had floods here and we've not been able to get into the office since the new year. Today, we've had to sit on hay bales on a trailer and be towed into the office by a friendly farmer and his tractor!

Anyway, glad to hear you've sorted out the problem. Just for the future, if you have the SQL code, open a new query, select View then SQL View and paste the SQL code (obviously, you will need to modify the field and table names to suit first). You can then revert to Design View and see the query as normal.

All the best.

shay

 

[JulieAsking]

No problems, Shay. Thanks for your reply and I hope you dry out soon!

Julie