HISDU

	UQ Home Contacts Study Maps News Events Library Give now my.UQ
	Health Information Systems Development Unit (HISDU)

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

Key	MyText
1	A
2	B
3	C
4	D

Table2

Key	myNum
1	10
3	20

Query1 Introduce a literal.
SELECT Key, "XYZ" AS myLit, myNum
FROM Table2;

Key	myLit	myNum
1	XYZ	10
3	XYZ	20

Query2 The bug.
SELECT Table1.Key, myText, myNum, myLit
FROM Table1 LEFT JOIN Query1 ON Table1.Key=Query1.Key;

Key	MyText	myNum	myLit
1	A	10	XYZ
2	B		XYZ
3	C	20	XYZ
4	D		XYZ

Query3 The fix.
SELECT DISTINCT * FROM Query1 WHERE myLit="XYZ";

Key	myLit	myNum
1	XYZ	10
3	XYZ	20

Query4 Bug Free!
SELECT Table1.Key, myText, myNum, myLit
FROM Table1 LEFT JOIN Query3 ON Table1.Key=Query3.Key;

Key	MyText	myNum	myLit
1	A	10	XYZ
2	B
3	C	20	XYZ
4	D

privacy | HISDU