Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2013
    Posts
    2

    Unanswered: Join Two Tables With Duplicates

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

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Why do you only have 3 x India in the resulting data set while 4 (2 from each table) would be expected ? What's the logic in the relationships between both tables?
    Have a nice day!

  3. #3
    Join Date
    Oct 2013
    Posts
    2
    @Sinndho: Thanks for your 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 need to get this in one table.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Charles_Access View Post
    @Sinndho: Thanks for your 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 need to get this in one table.
    That does not answer the question, at least not clearly.
    Have a nice day!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •