AnsweredAssumed Answered

Confused about Summary Fields and Reporting

Question asked by JuliaZhou on Mar 31, 2011
Latest reply on Mar 31, 2011 by philmodjunk


Confused about Summary Fields and Reporting


This is a very basic question, but for some reason I just cannot figure this out.  Any help would be very much appreciated!

I have a Doctors Table with fields: DoctorID, DoctorName.  I also have a Procedures table with fields: DoctorID, ProcedureName, ProcedureDate.  They are linked using the "DoctorID" field.  Each procedure that a doctor does is a record in the Procedures table. 

I want to create a report for a user entered set of dates that summarizes procedure count by doctor.  For example, if the user enters starting date "1/1/2011" and ending date "1/31/2011", then I want a report that looks like this:

StartingDate: 1/1/2011

EndingDate: 1/31/2011

DoctorName ProcedureName Count
John Smith ProcedureA 20
John Smith ProcedureB 31
John Smith ProcedureC 15
Bob Jones ProcedureA 51
Bob Jones ProcedureB 24

I only want procedures to be included in the count if "ProcedureDate" falls between "StartingDate" and "EndingDate".  How do I create this report?