Same qry structure, different results

  • Thread starter Thread starter JR
  • Start date Start date
J

JR

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.
 
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;
Click to expand...
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
 
Back
Top