chef chef - 1 year ago 53
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.

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