Join Two Tables With Duplicates

Question

I am trying to combine two peculiar tables in Microsoft Access and have been unable to do so even after doing a lot of brainstorming and searching on the internet.

The two tables are spend and export

Spend   
+-------------+--------+-------+-------+
|     Country | Metal  | Month | Spend |
+-------------+--------+-------+-------+
|     China   | Iron   | Jan   |   100 |
|     China   | Iron   | Feb   |   200 |
|     China   | Iron   | March |   300 |
|     India   | Iron   | Jan   |   400 |
|     India   | Copper | April |   500 |
|     Spain   | Zinc   | June  |   600 |
+-------------+--------+-------+-------+

Export
+-------------+-------+------+--------+
|     Country | Metal | Year | Export |
+-------------+-------+------+--------+
|     China   | Iron  | 2001 | 2,000  |
|     India   | Iron  | 2002 | 4,000  |
|     India   | Iron  | 2003 | 5,000  |
|     Spain   | Zinc  | 2011 | 3,500  |
|     Spain   | Zinc  | 2012 | 4,000  |
|     Spain   | Zinc  | 2013 | 9,000  |
+-------------+-------+------+--------+

Desired Combined Table

+-------------+--------+-------+-------+------+--------+
|     Country | Metal  | Month | Spend | Year | Export |
+-------------+--------+-------+-------+------+--------+
|     China   | Iron   | Jan   |   100 | 2001 | 2,000  |
|     China   | Iron   | Feb   |   200 |      |        |
|     China   | Iron   | March |   300 |      |        |
|     India   | Iron   | Jan   |   400 | 2002 | 4,000  |
|     India   | Iron   |       |       | 2003 | 5,000  |
|     India   | Copper | April |   500 |      |        |
|     Spain   | Zinc   | June  |   600 | 2011 | 3,500  |
|     Spain   | Zinc   |       |       | 2012 | 4,000  |
|     Spain   | Zinc   |       |       | 2013 | 9,000  |
+-------------+--------+-------+-------+------+--------+

Answer 1

Given your input tables the closest you'll get with a straight Select query is

SELECT 
    Spend.Country, 
    Spend.Metal, 
    Spend.Month, 
    Spend.Spend, 
    Export.Year, 
    Export.Export
FROM 
    Export 
    RIGHT JOIN 
    Spend 
        ON (Export.Metal = Spend.Metal) 
        AND (Export.Country = Spend.Country)
ORDER BY 
    Spend.Country, 
    Spend.Metal, 
    CDate("2010-" & [Spend].[Month] & "-01"), 
    Export.Year;

That query returns

Country  Metal   Month  Spend  Year  Export
-------  ------  -----  -----  ----  ------
China    Iron    Jan      100  2001    2000
China    Iron    Feb      200  2001    2000
China    Iron    March    300  2001    2000
India    Copper  April    500              
India    Iron    Jan      400  2002    4000
India    Iron    Jan      400  2003    5000
Spain    Zinc    June     600  2011    3500
Spain    Zinc    June     600  2012    4000
Spain    Zinc    June     600  2013    9000

...which includes the rows you specified. However, it does not suppress duplicate Month/Spend and Year/Export values from one row to the next, but that is something that can be done in a report.