We help IT Professionals succeed at work.
Get Started

Access syntax

LuckyLucks
LuckyLucks asked
on
120 Views
Last Modified: 2017-04-11
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
Comment
Watch Question
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This problem has been solved!
Unlock 1 Answer and 1 Comment.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE