Add a new field that enters Y or N

[Linda RQ]

Hi Everyone,

Using Access 2003 and I use the query grid to make my queries. I have a
query that I have 5 tables joined. The main tables for the data I am
reporting are tblPatients, tblPtThpy, tblTherapyType. The PtID in the
Patients table is related to PtID_fk in the tblPtThpy. The ThpyTypeID_fk in
the tblPtThpy is related to the TherapyTypeID in my tblTherapyType.

In my TherapyTypeID field I have criteria 70 or 81 or 97 or 121. This shows
me patients on different types of ventilators. 121 is a special type of
therapy that all ventilator patients should be on. I would like to remove
that critera from TherapyTypeID and add a new field that would display a yes
or no depending on if 121 is ordered.

Not sure if my thinking is right but I need to copy and paste this into
excel so another user can compare patients on this therapy with other
patients not on this therapy. If anyone has a better idea that would be
welcome too.

Thanks,
Linda

 

[John Spencer]

Build a query that shows all patients that ARE on ventilator type 121.

Now use that query and your tblPatients to build an unmatched query to show
every patient that is not in the list of patients with ventilator type 121.

SQL for first query would look like:
SELECT PtID_fk
FROM tblPtThpy Inner Join tblTherapyType
ON tblPtThpy.ThpyTypeID_fk = tblTherapyType.ThpyTypeID
WHERE tblTherapyType.ThypTypeID = 121

Second query might look like (this would show just the patients with type 121.
SELECT tblPatients.*
FROM tblPatients LEFT JOIN TheQuery
ON tblPatients.PtID = TheQuery.PtID_fk
WHERE TheQuery.PtID_Fk is Null

OR IF you wanted just a yes/no (-1 or 0) value you could use a subquery with
the Exists operator

SELECT tblPatients.*
, Exists(SELECT *
FROM tblPtThpy Inner Join tblTherapyType
ON tblPtThpy.ThpyTypeID_fk = tblTherapyType.ThpyTypeID
WHERE tblTherapyType.ThypTypeID = 121) as HasType121
FROM tblPatients


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County