Avatar of LuckyLucks
LuckyLucks
 asked on

Access syntax

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
* syntaxMicrosoft Access

Avatar of undefined
Last Comment
PatHartman

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
PatHartman

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck