Calculating Max appointments

[ET123]

I'm currently trying to this query in MS Access but can't. Pls help

1) Query to show the providers that have the highest # patients that come back for in person visit within 7 days of telephone visit. I've work on it below but can't run because of error. Can anyone help me to fix this?

SELECT a.provider_id, max(a.provider_id) as Max_provider_id, a.patient_id, a.appointment_type, a.appointment_date, a.show_code, a.appointment_time
FROM [Appointment Data] a
WHERE a.appointment_type='in-person visit'
and a.APPOINTMENT_DATE >= #12/1/2009# and a.appointment_date < #6/1/2010# and a.show_code = 'Y'
and exists(select appointment_date from [Appointment Data] where patient_id=a.patient_id and (appointment_type='telephone visit' and show_code = 'y' and appointment_date >= #12/1/2009# and appointment_date <#6/1/2010# and appointment_time >= #17:30#) and datediff('d',appointment_date,a.appointment_date) <= 7)
group by provider_id;

Error Message is: you tried to execute a query that does not include the specified expression 'patient_id' as part of an aggregated function



Thank you!

[pbaldy]

It looks like you need GROUP BY clause. Generally speaking, when you have aggregate functions like Sum, Max, etc in the SELECT clause, you need a GROUP BY clause. Any field which which is not part of an aggregate function (like patient_id) would be in the GROUP BY clause. If you hit the Totals button with the query in design view, you should see where you can set that.

[NTC]

pbaldy advice is always correct. but let me add - since you self describe as a novice; your 'where' statement is complex. For the novice - these type queries don't have to be accomplished in one sql statement. Consider using the query design grid and first make a plain ole select query for "person visit within 7 days of telephone visit"...make that work and give it a name. then do an aggregate query using this first query as the record source (look for the epsilon E icon). Any Access textbook will have an example of aggregate (sum, count, max) queries. This approach is generally more accessible to beginners.