Access Query Issue (Access 2002)

[Dmcdan]

Hey Gang,

I have a database that I need to write a report for. I have all the reports done but 1. I need a query that will allow me to get the information based on the following criteria.

I need to know what patients has had one procedure done but not another. They exists in the same field. How do I write a query that would allow me to find out who has had a shot but not a pill??

Thanks,

IrishDan

[NYIntensity]

Dan,
So we'll skip the lecture on why we don't keep two criteria in one field <img src=/S/grin.gif border=0 alt=grin width=15 height=15>...how is the information set up? What are the exact verbages for the options? A list of some example choices would be great...a stripped down copy of the database (just some example data) would be preferred <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

[johnhutchison]

I attach a demo showing how you would do it with one table structure.

My demo needs three queries to do this.

First query find the list of shot patients, second find a list of pill patients.

The third joins these together, but uses a left join, so all shot patients are returned, then tests for Null on the pill patients side of the join.