Left Join works with table but fails with query
- by Frank Martin
The following left join query in MS Access 2007
SELECT
Table1.Field_A,
Table1.Field_B,
qry_Table2_Combined.Field_A,
qry_Table2_Combined.Field_B,
qry_Table2_Combined.Combined_Field
FROM Table1
LEFT JOIN qry_Table2_Combined
ON (Table1.Field_A = qry_Table2_Combined.Field_A)
AND (Table1.Field_B = qry_Table2_Combined.Field_B);
is expected by me to return this result:
+--------+---------+---------+---------+----------------+
|Field_A | Field_B | Field_A | Field_B | Combined_Field |
+--------+---------+---------+---------+----------------+
|1 | | | | |
+--------+---------+---------+---------+----------------+
|1 | | | | |
+--------+---------+---------+---------+----------------+
|2 |1 |2 |1 |John, Doe |
+--------+---------+---------+---------+----------------+
|2 |2 | | | |
+--------+---------+---------+---------+----------------+
[Table1] has 4 records, [qry_Table2_Combined] has 1 record.
But it gives me this:
+--------+---------+---------+---------+----------------+
|Field_A | Field_B | Field_A | Field_B | Combined_Field |
+--------+---------+---------+---------+----------------+
|2 |1 |2 |1 |John, Doe |
+--------+---------+---------+---------+----------------+
|2 |2 |2 | |, |
+--------+---------+---------+---------+----------------+
Really weird is that the [Combined_Field] has a comma in the second row. I use a comma to concatenate two fields in [qry_Table2_Combined].
If the left join query uses a table created from the query [qry_Table2_Combined] it works as expected.
Why does this left join query not give the same result for a query and a table? And how can i get the right results using a query in the left join?
