SQL Question
Access query to include all records even if one field has missing data (is Null)
I am trying run a query to include all records. it is omitting records if one of the fields is null. Changing the "output all fields" in query properties to Yes brings in all fields but that is not what i need. i need all records. Here is my SQL query:
SELECT tblBOOK.Concat, tblBOOK.Owner1, tblBOOK.Owner2,
tblIndustry.[Industry Name], tblOwner1.Points, tblOwner2.Points,
tblIndustry.[Industry Points], tblBOOK.FILE, tblBOOK.SCORE,
tblFILE.Points, tblSCORE.Points, tblBOOK.Risk, tblRisk.Points,
[tblOwner1]![Points]+[tblOwner2]![Points]+[Industry Points]+
[tblFILE]! [Points]+[tblSCORE]![Points]+[tblRisk]![Points]
AS TtlPoints
FROM tblRisk
INNER JOIN (tblSCORE
INNER JOIN (tblFILE
INNER JOIN ((tblOwner1
INNER JOIN (tblOwner2
INNER JOIN tblBOOK
ON (tblOwner2.Secondary = tblBOOK.Owner2)
AND (tblOwner2.Primary = tblBOOK.Owner1))
ON tblOwner1.Primary = tblOwner2.Primary)
INNER JOIN tblIndustry
ON tblBOOK.Industry = tblIndustry.[Industry Name])
ON tblFILE.Score = tblBOOK.FILE)
ON tblSCORE.Score = tblBOOK.SCORE)
ON tblRisk.Factor = tblBOOK.Risk;
I also tried to add "IsNull" to the criteria and then in returns no records.
Recommended for you: Get network issues from WhatsUp Gold. Not end users.
Answer Source
I needed to change these from INNER JOINS to LEFT JOINS
SELECT tblBOOK.Concat, tblIndustry.[Industry Name],tblIndustry.[Industry Points],
tblOwner1.Primary, tblOwner1.Points, tblFILE.Score, tblFILE.Points, tblCARD.Score,
tblCARD.Points, tblRisk.Factor, tblRisk.Points,
[Industry Points]+[tblOwner1]![Points]+[tblFILE]![Points]+[tblCARD]![Points]+
[tblRisk]![Points]
AS TotalPoints
FROM ((((tblBOOK
LEFT JOIN tblIndustry ON tblBOOK.Industry = tblIndustry.[Industry Name])
LEFT JOIN tblOwner1 ON tblBOOK.Owner1 = tblOwner1.Primary)
LEFT JOIN tblFILE ON tblBOOK.CSS = tblFILE.Score)
LEFT JOIN tblCARD ON tblBOOK.CARD = tblCARD.Score)
LEFT JOIN tblRisk ON tblBOOK.Risk = tblRisk.Factor;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download