Show an empty query field when there is no underlying table data

[john.mctigue]

I have two tables containing information on stem cell transplants and
the therapies that patients have received related to

that transplant. The tables, somewhat simplified, look like:

tbl_transplant:
idper
namesur
namegive
datebmt

tbl_therapy:
idther
idper
type
name

There are 27 unique types of therapy. I want to extract a single row
of data per transplant in a query to show two

particular therapies, CON and GVH. In my query below I have included
the therapy table twice, once to pull out CON and the

other to pull out GVH.

If there is no CON or GVH record matching the transplant record I
would like the output row to show blank fields where there

is no data eg:
idper namesur namegive datebmt tbl_therapy_1.type tbl_therapy_1.name
tbl_therapy.type

tbl_therapy.name
241 Bloggs Joe 01/01/1996 CON Cyclophosphamide GVH MAB
586 Soap Joe 01/01/2005 CON BEM <<blank>> <<blank>>

(Apologies if the formatting of the above is all over the place. It
was fine in Notepad with Courier font and no word wrap.)

At present I don't see Joe Soap's record at all because there is no
GVH therapy associated with that transplant. I

understand why I am getting the results I am getting but can't see how
to get the results I would like.

SELECT tbl_transplant.idper, tbl_transplant.namesur,
tbl_transplant.namegive, tbl_transplant.datebmt, tbl_therapy_1.type,

tbl_therapy_1.name, tbl_therapy.type, tbl_therapy.name
FROM (tbl_transplant LEFT JOIN tbl_therapy AS tbl_therapy_1 ON
tbl_transplant.idper = tbl_therapy_1.idper) LEFT JOIN

tbl_therapy ON tbl_transplant.idper = tbl_therapy.idper
WHERE (((tbl_therapy_1.type)="CON") AND ((tbl_therapy.type)="GVH"));

Any help would be appreciated.

 

[Ken Sheridan]

By restricting the LEFT OUTER JOINS on the tables on the outer side of the
join this in effect makes them INNER JOINS. Hence the results you are
getting.

You could use subqueries to return the therapies for each patient and
restrict the outer query to those patients who have received either CON or
GVH therapies:

SELECT idper, namesur, namegive,
(SELECT type
FROM tbl_therapy
WHERE tbl_therapy.idper = tbl_transplant.idper
AND type = "CON") AS TherapyType1,
(SELECT name
FROM tbl_therapy
WHERE tbl_therapy.idper = tbl_transplant.idper
AND type = "CON") AS TherapyName1,
(SELECT type
FROM tbl_therapy
WHERE tbl_therapy.idper = tbl_transplant.idper
AND type = "GVH") AS TherapyType2,
(SELECT name
FROM tbl_therapy
WHERE tbl_therapy.idper = tbl_transplant.idper
AND type = "GVH") AS TherapyName2
FROM tbl_transplant
WHERE EXISTS
(SELECT *
FROM tbl_therapy
WHERE tbl_therapy.idper = tbl_transplant.idper
AND type LIKE ("CON","GVH");

As each subquery must return a single value this assumes of course that
there can be only one row in the tbl_therapy table per patient for either the
CON or GVH therapy.

BTW I'd recommend that you avoid using terms like 'type' or 'name' as column
names as both are the names of built in properties in Access. If you have
any problems with them in the query then wrapping them in brackets [type],
[name] should help, but its better to use more explicit terms like
therapytype, therapyname.

Ken Sheridan
Stafford, England