Go to The University of Queensland Homepage
Welcome to the Faculty of Arts Homepage You are at the Faculty of Arts site

Microsoft Access SQL bug

There are many bugs in Microsoft Access. Here's one that I found recently.
LEFT JOIN a table to a query where the query has a literal in the SELECT clause will replicate the literal for non joined records.
Table1
KeyMyText
1A
2B
3C
4D
Table2
KeymyNum
110
320
Query1 Introduce a literal.
SELECT Key, "XYZ" AS myLit, myNum
FROM Table2;
KeymyLitmyNum
1XYZ10
3XYZ20
Query2 The bug.
SELECT Table1.Key, myText, myNum, myLit
FROM Table1 LEFT JOIN Query1 ON Table1.Key=Query1.Key;
KeyMyTextmyNummyLit
1A10XYZ
2BXYZ
3C20XYZ
4DXYZ
Query3 The fix.
SELECT DISTINCT * FROM Query1 WHERE myLit="XYZ";
KeymyLitmyNum
1XYZ10
3XYZ20
Query4 Bug Free!
SELECT Table1.Key, myText, myNum, myLit
FROM Table1 LEFT JOIN Query3 ON Table1.Key=Query3.Key;
KeyMyTextmyNummyLit
1A10XYZ
2B
3C20XYZ
4D