Hi:
I need to get this syntax right in MS Accesss.
So we lookup first table MAP_TBL on 3 columns to match and they all match then we return the MYFLD in that table.
If all 3 columns do not match then we lookup the second table MAP_TBL1 on 2 column match and if they both match then we return the MYFLD in that table.
If there is no match in either then we return "OTHER".
SELECT
IIF(MAP_TBL.[MYFLD] IS NOT NULL, MAP_TBL.[MYFLD] , (MAP_TBL_1.[MYFLD] IS NOT NULL,MAP_TBL_1.[MYFLD], "OTHER" ) ) AS final_value
froM
MYTABLE1
left join MAP_TBL ON MYTABLE.COL1= MAP_TBL.COL1 AND MYTABLE.COL2= MAP_TBL.COL2 AND MYTABLE.COL3= MAP_TBL.COL3
left join MAP_TBL1 ON MYTABLE.COL1 = MAP_TBL1.COL1 AND MYTABLE.COL2 = MAP_TBL1.COL2
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.