And query from same field

[Futtecr]

I have been asked to query a database where the daughter table(many) is in a 1:many relationship with its parent table. In 1 particular field, Descript_Name, there is a list of dffferent procedures done on a patient. I need to query for the presence of BOTH "Mechanical Ventilation" and "Percutaneous Central Venous Catheter." These would be listed in different rows of the same field. When I do an "or" query I get all patients who have 1 or both. However, when I do an "And" query I get 0 results and I know that is not correct.



Other information which may (or may not) be helpful:
Access 2010
The parent and daughter tables are linked and I ask of the parent table The CaseID and ICUadmDateTime (with a date parameter on this field, >#1/1/2010#).
Thanks in advance
Craig

[JoeM]

Here is one way to do it.

Create a calculated field that returns a 1 if Descript_Name="Mechanical Ventilation", and a 0 if it does not.
Then create another calculated field that returns a 1 if Descript_Name="Percutaneous Central Venous Catheter", and a 0 if it does not.

Then perform an Aggregate Query that Groups By Patient, and sums up these two calculated fields. If you place criteria in these two Summed fields to only return records where the value is greater than 0, it should only return patients who have had both procedures.

[Futtecr]

JoeM,
First of all, Thanks.
I created to calculated fields:
ExprCVL: IIf([Int_Procs]![Descript_Name]="Percutaneous Central Venous Catheter",1,0)
ExprVent: IIf([Int_Procs]![Descript_Name]="Mechanical Ventilation, Conventional (including CPAP plus Pressure Support)",1,0)
I get a bunch of records for each patient and some patients have a "1" in different rows. I am not sure how to perform an aggregate query. Can you guide me on that?
Thanks

[JoeM]

Access' built-in help files should be able to help you out on creating Aggregate (Totals) Queries. There are also many tutorials out there if you Google it.

Essentially, you are just going to create a new query based on the first query with the calculations, adding in the Patient field and the two calculated fields.
Then click on the Totals button, and select the Totals Row options of "Sum" for the two calculated fields, and then add the criteria of > 0 under each calculated field.