Blog posts about access

  • Access: Unmatched query for more than one field

    February 12, 2009 | Tags: Access, Troubleshooting
    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).