Occurences of multiple fields

[switchflare]

This is likely an easy question, but I am not familiar enough with Access SQL and its syntax to tackle it in a timely manner. I have two tables. I have already successfully created one query that pulls the doctor name from one table and appointment date from the other table based on the patient ID. Appointment date is also converted to mm/yyyy in this first query.

Two fields of the query output look roughly like this:
Doc_name Appt_Date
SMITH 09/2007
TORRES 08/2007
SMITH 08/2007
JOHNS 10/2007
SMITH 09/2007
SMITH 09/2007

I keep hitting snags on this next query. I need to count the number of times a combination of Doc_name and Appt_Date are the same. In real life terms, how many appointments come from each doctor in a given month?

Desired Output using above data should yield this:
Doc_name Appt_Date Count
SMITH 08/2007 1
TORRES 08/2007 1
SMITH 09/2007 3
JOHNS 10/2007 1

Do I need to create an intermediary table? or can I just bank off the query I have already created? Can anyone provide the proper way to do this with the correct syntax? Thanks!

 

[pbaldy]

Try

SELECT Doc_name, Appt_Date, Count(Doc_Name) AS HowMany
FROM QueryName
GROUP BY Doc_name, Appt_Date

Paul
MS Access MVP 2007/2008

 

[switchflare]

Pathetically simple. Thanks.