I have a db with patient names that appear multiple of times with multiple different procedures. Is it possible to query all patients who have had procedure X AND procedure Y AND procedure Z?

Using AND in the criteria returns no data.

You can do "or"
procedure X OR procedure Y OR procedure Z

Then "group by" name and "count" the # of procedures, then "having" count = 3 will have the patients that have the 3 procedures.

Just to be clear, 'AND' is not a function that can be used in a criteria? Is that correct?

The point is that AND will not find what you want.
If the patient has 2 records one for procedure X and 1 for procedureY then the Or will find both , but the AND will always fail as it is infact saying that the field is equal to all 3 at the same time.

Brian

Please show me where the group by clause should be in this SQL.

Group By Patient_Name

SELECT [Pt_Test Listing].MPI, [Pt_Test Listing].Patient_Name, [Pt_Test Listing].Procedure, [Pt_Test Listing].[Service Date], [Pt_Test Listing].Dept, [Pt_Test Listing].Category
FROM [Pt_Test Listing]
WHERE ((([Pt_Test Listing].Dept)="NIV" Or ([Pt_Test Listing].Dept)="CCL" Or ([Pt_Test Listing].Dept)="CT" Or ([Pt_Test Listing].Dept)="MRI" Or ([Pt_Test Listing].Dept)="IVS"))
ORDER BY [Pt_Test Listing].Patient_Name

You cannot just Groupby one field in the Select Clause unless the other filds are part of the aggegate , switch to design view and select a Totals query then take it from there.


Brian

Thank you. I accomplished the 'group by' on the report side. I'll keep your suggestion though, as this is going to be a hughe every growing project.