troubleshooting Question

Access syntax

Avatar of LuckyLucks
LuckyLucks asked on
* syntaxMicrosoft Access
1 Comment1 Solution121 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 1 Comment.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros