By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,289 Members | 1,277 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,289 IT Pros & Developers. It's quick & easy.

Including Nulls in a Query - Query not showing all records

P: 14
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:
Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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
  3. WHERE ((([Table Follow Up post RT].oesophagus) Is Not Null) AND (([Table Follow Up post RT].lung) Is Not Null))
  4. ORDER BY [Table Demographics].hosp_no;
I am using Access 2000 on windows XP

Any help would be greatly appreciated.
Feb 15 '08 #1
Share this Question
Share on Google+
1 Reply

Expert Mod 2.5K+
P: 2,545
Here is the SQL for the query:
Expand|Select|Wrap|Line Numbers
  1. 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
  2. WHERE ((([Table Follow Up post RT].oesophagus) Is Not Null) AND (([Table Follow Up post RT].lung) Is Not Null))
  3. 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.


Feb 15 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.