Developer Forum

Please, does anyone have any insights as to why the first
of the two queries below functions as desired while the
second does not?

The following query does what I want it to do, which is
provide 0 (zero) fills whenever there is an "SRC" in
tblAllSRCs.SRC but not in qryTotalbySRC.

SELECT tblAllSRCs.SRC, AllSRCs.Title, AllSRCs.Strength,
qryTotalSRC.[SumOfSumOfST LBS]
FROM tblAllSRCs LEFT JOIN qryTotalbySRC ON
tblAllSRCs.SRC = qryTotalSRC.SRC;


The following query does NOT do what I want it to do.
It omits the SRC from tblAllSRCs.SRC when there is no
corresponding SRC in qryIXSRCList.

SELECT tblAllSRCs.SRC, AllSRCs.Title, AllSRCs.Strength,
[qryIXSRCList].COorST
FROM tblAllSRCs LEFT JOIN [qryIXSRCList] ON tblAllSRCs.SRC
= [qryIXSRCList].SRC;

Thanks.

"JR" wrote
Hi JR,

The only way I can imagine that the first query would
*not* return "empty" [SumOfSumOfST LBS] is if
qryTotalbySRC.SRC values exist for every tblAllSRCs.SRC.

You could test that by changing the first query to:

SELECT tblAllSRCs.SRC, tblAllSRCs.Title, tblAllSRCs.Strength,
qryTotalSRC.[SumOfSumOfST LBS]
FROM tblAllSRCs LEFT JOIN qryTotalbySRC ON
tblAllSRCs.SRC = qryTotalSRC.SRC
WHERE [SumOfSumOfST LBS] IS NULL;

My guess is that it will not return any records
because qryTotalSRC has provided values
for all SRC that occur in tblAllSRCs.

You can wrap [COorST] in the Null-to-zero function (NZ)
to "see" zeroes in the second query.

SELECT tblAllSRCs.SRC, tblAllSRCs.Title, tblAllSRCs.Strength,
NZ([qryIXSRCList].COorST, 0)
FROM tblAllSRCs LEFT JOIN [qryIXSRCList] ON tblAllSRCs.SRC
= [qryIXSRCList].SRC;

Please respond back if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter