Including Nulls in a Query - Query not showing all records

[babamc4]

Hi All,

I am trying to run a query to include null fields. My tables are as follows:
Table Demographics 54 records (Main table)
Table Follow Up During Radiotherapy 100+ records (Sub table)
Table Follow Up Post RT 100+ (Sub table)
Table Last Contact 54 records (Sub table)

All are one to many except for Table Last Contact which is one to one.

All patients on trial are in Table Demographics but not all patients are in Follow Up during and post radiotherapy. I am trying to show all the records in the Table Demographics with the rest of the tables and to include nulls, but nothing seems to work. I have tried to do the following:

1. Inner Join, Outer Join, Right Outer and Left Outer join
2. Clicking on the join properties and picking option 2.
3. I've searched the internet and the Outer join seems to be the correct join but it is still not giving me all the records from the Table Demographics

Here is the SQL for the query:

SELECT [Table Demographics].hosp_no, [Table Demographics].ID, [Table Demographics].gender, [Table Demographics].DOB, [Table Demographics].weight, [Table Follow Up post RT].weight, [Table Demographics].chemo, [Table Demographics].histology, [Table Demographics].staging, [Table Demographics].DOD, [Table Demographics].[Histological Diagnosis], [Table Demographics].date_consent, [Table Demographics].status, [Table Demographics].[3_mth_status], [Table Last Contact].[Last Contact], [Table Last Contact].Source, [Table Follow Up post RT].date, [Table Follow Up post RT].oesophagus, [Table Follow Up post RT].lung, [Table Demographics].[GyMax dose PTV], [Table Follow up during radiotherapy].week, [Table Follow up during radiotherapy].weight, [Table Follow up during radiotherapy].oesophagus, [Table Follow up during radiotherapy].heart
FROM (([Table Demographics] INNER JOIN [Table Follow up during radiotherapy] ON [Table Demographics].hosp_no = [Table Follow up during radiotherapy].hosp_no) INNER JOIN [Table Follow Up post RT] ON [Table Demographics].hosp_no = [Table Follow Up post RT].hosp_no) INNER JOIN [Table Last Contact] ON [Table Demographics].hosp_no = [Table Last Contact].hosp_no
WHERE ((([Table Follow Up post RT].oesophagus) Is Not Null) AND (([Table Follow Up post RT].lung) Is Not Null))
ORDER BY [Table Demographics].hosp_no;

I am using Access 2000 on windows XP

Any help would be greatly appreciated.

[Stewart Ross]

Here is the SQL for the query:

SELECT .... FROM (([Table Demographics] INNER JOIN [Table Follow up during radiotherapy] ON [Table Demographics].hosp_no = [Table Follow up during radiotherapy].hosp_no) INNER JOIN [Table Follow Up post RT] ON [Table Demographics].hosp_no = [Table Follow Up post RT].hosp_no) INNER JOIN [Table Last Contact] ON [Table Demographics].hosp_no = [Table Last Contact].hosp_no
 WHERE ((([Table Follow Up post RT].oesophagus) Is Not Null) AND (([Table Follow Up post RT].lung) Is Not Null))
ORDER BY [Table Demographics].hosp_no;

Hi there. Two observations: firstly, your Where clause conflicts with your requirement to show nulls, 'cos you are only selecting rows where there is an oesophagus AND lung follow-up (or so it would appear).

Secondly, you want to show all rows from the demographics table, and only the rows from the other tables where there are any. This will involve the use of a Left join between the demographics table and all others, not the equijoins you currently have (the Inner Joins are equijoins, which will restrict the rows displayed to just those where all the joined fields are equal).

Fix the two problems and the query should do what you need it to do.

Cheers

Stewart