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 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;