Access: Unmatched query for more than one field
The limitations of Access can be real troublesome sometimes. Like for instance when trying to outer join a table based on more than one field. The unmatched join in Access allows only one field for the join, but there's a little work-around to that.
For instance, if we want to join table A with B on the fields fld1 and fld2 and list all posts in A wether or not they are matched in B, the SQL expression for Access would look something like this:
SELECT * FROM A LEFT JOIN B ON (A.fld1=B.fld1) WHERE ((B.fld2=A.fld2) OR (B.fld2 IS NULL))
Notice the second part that says that B.fld2 either has to be the same as A.fld2 or not a value (which would be the case if our initial criteria A.fld1=B.fld1 doesn't match).
Related posts: