The problem is that Field2 may not be what Field2 is supposed to be. That is, if Field 2 is equal to Field3, then fine. However, if not, then it has to be something else.
SELECT Table1.Field1, Table1.Field2, Table1.Field3, Table2.Field4 FROM Table1 LEFT OUTER JOIN Table2 ON Table1.Field1 = Table2.Field1 AND Table1.Field2 = Table2.Field2
Basically anytime Field2 <> Field3 it needs to be something else (a known value depending on the actual value of Field2). There are only four of these, and they will never change.
Field2 Is Field3 Is Field2 Needs to be FRED FRED FRED FRED ETHYL LUCY ADAM ADAM ADAM ADAM EVE LILITH And so on
From novice to tech pro — start learning today.