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.
1 2759
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
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: PST |
last post by:
Here's a problem I'm trying to deal with:
I'm working on a Frontpage 2000 website for a boat handicapping
system, built in Access 97.
What I'm trying to accomplish is:
The user enters a boat name in a text field OR a sail number in a text
field, and gets the desired boat record back (an exact match).
|
by: Salad |
last post by:
A97.
Situation: I have 3 tables with a text field in each and a date field
in the first 2 tables:
Table1 Text1, Date1
Table2 Text2, Date2
Table3 Text3 (no date field)
The following makes up a saved query called Query1
|
by: Marcus |
last post by:
Wondering if it's possible to allow for Null enteries in SQL/VB code.
The example below doesn't work. Am I missing something? Or, is this
just not possible?
strSQL = "SELECT * FROM tblData " & _
"WHERE " & "tblData.Date = #" & !!
& " Or (" & !! Is Null & ")# AND (" &
strCriteria & ");"
|
by: teddysnips |
last post by:
I have to write an application to do some data cleansing. It's a
Contact database, but over a number of years there are multiple
companies which are all essentially the same entity. For each company
there are also multiple contacts, some of which may be congruent.
It's a very simple app. with only two forms. The first form shows two...
|
by: Thomas |
last post by:
Hi All. I think that if I explain the database layout first it may be
easier to ask my question.
Table Name Field Name
Test TestCode
Description
SpecimenRequirements
FeeSchedule TestCode
CPTCode
| |
by: Ron |
last post by:
Hi,
Does a make table query have limitations that a select query doesn't have?
This is my problem. I've got a make table query I'll call qrymktblTemp--it
combines a few different tables and queries to form a new table that I then
manipulate in various ways. It's worked well for all the records that
qualify (like, it only includes...
|
by: lmawler |
last post by:
Hi,
I am executing sql from VB to populate a form... (I'm building a search, so my code takes what the user inputs, queries the database, and then populates a table, to which the fields on my form are tied.)
The query runs fine (compiles and runs without throwing any errors). I can see in my "watch" in the debugger that the variable has the...
|
by: ManningFan |
last post by:
Go into a table where you have a field that has NULL values. Right-
Click on a record with a value in that field and choose "Filter
Excluding Selection". Some (or most, or all) of your records where
there is a NULL value in that field get eliminated.
Without getting crazy (i.e. designing your own bottons and "re-
inventing the wheel"), is...
|
by: jsacrey |
last post by:
Hey everybody, got a secnario for ya that I need a bit of help with.
Access 97 using linked tables from an SQL Server 2000 machine.
I've created a simple query using two tables joined by one field
between them. The join field in both tables are indexed and I'm
selecting 1 field from each table to lookup. The Access query is
taking more...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |