same qry using different component = different results?

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

JR

Please, can someone point out 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 qryTotal_V_bySRC.

SELECT tblAllSRCs.SRC, AllSRCs.Title, AllSRCs.Strength,
qryTotal_V_bySRC.[SumOfSumOfAT LBS],
qryTotal_V_bySRC.[SumOfSumOfDE LBS],
qryTotal_V_bySRC.[SumOfSumOfSE LBS],
qryTotal_V_bySRC.[SumOfSumOfST LBS]
FROM tblAllSRCs LEFT JOIN qryTotal_V_bySRC ON
tblAllSRCs.SRC = qryTotal_V_bySRC.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 qryTotal_ClassV_bySRC.

SELECT tblAllSRCs.SRC, AllSRCs.Title, AllSRCs.Strength,
[qry IX --> 06) Final SRC List (LBS/SRC/DAY)].SCENARIO,
[qry IX --> 06) Final SRC List (LBS/SRC/DAY)].COorAT,
[qry IX --> 06) Final SRC List (LBS/SRC/DAY)].COorDE,
[qry IX --> 06) Final SRC List (LBS/SRC/DAY)].COorDF,
[qry IX --> 06) Final SRC List (LBS/SRC/DAY)].COorRE,
[qry IX --> 06) Final SRC List (LBS/SRC/DAY)].COorST
FROM tblAllSRCs LEFT JOIN [qry IX --> 06) Final SRC List
(LBS/SRC/DAY)] ON tblAllSRCs.SRC = [qry IX --> 06) Final
SRC List (LBS/SRC/DAY)].SRC;
 
Hi,


That is probably not your real queries since none should work at all due
to a typo problem: you use tblAllSRCs and AllSRCs (without the tbl prefix,
and by the way, I suggest you don't use those prefix) at the same time.
Since those are not the real queries, Í can't tell what the real ones do,
but I suspect you use a WHERE clause, inappropriately.

In theory, both queries should supply a NULL value, not a zero, under
the "second table" fields when that second table has no matching SRC value,
and should return all the records from the AllSRCs table.

If you use a WHERE clause involving the "second table", be sure to
account for NULL:

WHERE rightTable.Field = 22
is wrong

WHERE rightTable.Field = 22 OR rightTable.Field IS NULL
is the way to go. Indeed, the outer join (here, the left join) job is
to eventually produces NULL value, so, the WHERE clause has to deal with
those null, else, a simple inner join would produce the same result, and
faster, and somehow easier to manage (avoiding ambiguous join).


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top