Managing unmatched results of JOIN in MS ACCESS

Question

I have a table with "CODE", "DATA" columns and a table with "OLD CODE","NEW-CODE" columns used to map old code to new one. I need to perform a Join in order to get data with new codes, but only where available, otherwise I need to keep the old code. Is there a way to do something like "LEFT JOIN OR SELF" in order to set the value on the left side of the join for cases where the matching fails?

CODE    DATA
-        -
A        30
B        34
C        5
D        10

And this is the second Table

  OLD CODE  NEW CODE

    B       Ba1
    C       Ca2
    D       Da3

Now the expected result would be DATA from the first table with new code if available otherwise the old one.

CODE    DATA

A      30
Ba1    34
Ca2    5
Da3    10

Add some sample table data and the expected result - all as formatted text, not images. Also show us your current query attempt. — jarlh, Mar 08 '18 at 09:05
Possible duplicate of [How do I write a full outer join query in access](https://stackoverflow.com/questions/19615177/how-do-i-write-a-full-outer-join-query-in-access) — Erik A, Mar 08 '18 at 09:13

Yogesh Sharma · Accepted Answer · 2018-03-08T10:55:41.887

2

Use LEFT OUTER JOIN in order to get Old Code if New Code not exists

SELECT 
       IIF(s.newcode IS NULL, f.Code, s.newcode) AS Code, 
       f.DATA 
FROM FirstTable f
LEFT OUTER JOIN SecodTable s ON s.OLDCODE = f.CODE

edited Mar 08 '18 at 10:55

answered Mar 08 '18 at 09:08

Yogesh Sharma

49,870
5
26
52

I cannot get this one to work, could it be that MS ACCESS does not support COALESCE? – Jack Mar 08 '18 at 10:38

Managing unmatched results of JOIN in MS ACCESS

1 Answers1