Join Two Tables With Duplicates

[Charles_Access]

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. I will really appreciate if someone could help me.

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 |
+-------------+--------+-------+-------+------+--------+

 

[plog]

First and leastmost, 'Year' and 'Month' are poor field names because they are reserved words in Access and can make coding harder. I'd change them by prefixing them with the data they are for (i.e. SpendMonth, ExportYear).

The main issue you have is that from the computer's perspective the values in your Month field does nothing for you. You and I know that October comes before November which comes before December. But when you have that data stored as text, the computer thinks the order is December - November - October. If that data was numeric (Oct=10, Nov=11, Dec=12) the computer could order it correctly.

And it seems ordering it correctly, to find the minimum month in a Country/Metal permutation of the Spend table, is the key to getting the final results you want. So the first thing you need to do is convert the Month values to numbers. I suggest you create a new field (called SpendMonth) that is numeric and running an append query to populate it with the correct value based on the Month field.

From there, you would create a sub query on the Spend table to find the minimum value for SpendMonth in every Country/Metal permutation. You would then create a new query based on that query, Spend and Export. They key to this query is linking them correctly. Link Spend to Export by Country and Metal fields, then make it a LEFT JOIN (http://www.w3schools.com/sql/sql_join_left.asp) from Spend to Export. This means you show all records from Spend. Link Spend to the subquery by Country, Metal and the Minimum SpendMonth value in the sub-query to the SpendMonth field in Spend. Make this a LEFT JOIN from Spend to the sub-query to show all the records in Spend.

Bring in all the fields in Spend to show, then you will make 2 conditional fields. 1 will show the Year value from Export if there is a a matching record in the sub-query. The other conditional field will show the Export field from the export table if there is a matching record in the sub-query.

I know that's a lot of instructions, but work through it and post back any questions you have. Again, the key initial step is converting your Month field to numeric data.

 

[Charles_Access]

@Plog: Thanks for your detaileds reply. Actually, I have created dummy tables to replicate real data that I have. In this case, for example the month could be replaced by say analysts name. Hence the second table is showing expected export amount as per an analyst. Now, same analyst could give multiple export figures for a country and metal combination or multiple analyst could give export figures for a country and metal combination. That is why I would want my combined table to have all records from first table and also all records from second table, but I would not want spend figures from first table to be repeated.