Question: I have a fairly simply question, but I happen to know not enough to answer it myself. I have a table with monthly financial data and I have another table with which I do some mapping. In both tables I have the corresponding three fields / columns:

* JV Segment Account
* JV CAPEX TECH AREA
* Movement code

I need to join always on "Movement Code" but when JV Segment Account is "[None]" I need to join in combination with "JV Capex tech Area" and when "JV Capex tech area" is "[None]" I need to join with "JV Segment Account". So it's a double join (always including movement code but with a variable second join). Is it possible to do this in one query?

[![enter image description here](https://i.stack.imgur.com/lG5Z8.jpg)](https://i.stack.imgur.com/lG5Z8.jpg)

Thanks in advance,

Michiel

Answer: 
The following is one way to get your answer. To translate this back to your tables and fields:

* ID = Movement Code - which matches in both, thus the Inner Join
* FldA = JV Segment Account
* FldB = JV Capex tech area
* If fields match between tables, then select

```
SELECT Table1.ID, Table1.FldA, Table1.FldB, Table2.FldA, Table2.FldB, 
    IIf([Table1].[FldA]=[Table2].[FldA],'Match',"") AS Same2, 
    IIf([Table1].[FldB]=[Table2].[FldB],'Match',"") AS Same3
    FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID
    WHERE (((IIf([Table1].[FldA]=[Table2].[FldA],'Match',""))="Match")) 
    OR (((IIf([Table1].[FldB]=[Table2].[FldB],'Match',""))="Match"));

```
Consider a union query:

```
SELECT * 
FROM MonthlyFinancialTable f
INNER JOIN MapTable m
ON f.[Movement code] = m.[Movement code]
AND f.[JV CAPEX TECH AREA] = m.[JV CAPEX TECH AREA]
WHERE f.[JV Segment Account] = '[NONE]'

UNION ALL

SELECT * 
FROM MonthlyFinancialTable f
INNER JOIN MapTable m
ON f.[Movement code] = m.[Movement code]
AND f.[JV Segment Account] = m.[JV Segment Account]
WHERE f.[JV CAPEX TECH AREA] = '[NONE]'

```