Percentage of a Count by Group

[simon4amiee]

Hi guys, should be doable, I have a very simple query which brings back a count of records. I would like to add a percentage column to this which shows a percentage by ClinicCode. I've attached some sample data and what the inteded outcome should be.

Any help would be appreciated.

 

[plog]

You are going to need a total of 3 queries to pull this off, A, B & C, where C is the final query that displays the results you posted. Your current query is A.

You need to build B using A. It will be a totals query to get the total number of AppointmentIDs by ClinicCode. Call that field [TotalClinicAppointments].

Then to build C, you use A & B. You link them via ClinicCode, bring in all the fields you want to display and for your percentage you divide [CountOfOPAppointmentID] by [TotalClinicAppointments].

Give it a shot and post back any issues you have. Be sure to post SQL of where you are stuck.