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

[Dal Jeanis]

1) Change the name of the fields Month and Year, because they're reserved words and will cause you untold grief.

2) Assuming these were the names of the tables and fields...

tblSpend
   Country
   Metal
   SpMonth
   Spend

tblExport
   Country
   Metal
   ExpYear
   Export

Then here's your query...

SELECT TE.Country, TE.Metal, TS.SpMonth, TS.Spend, TE.ExpYear, TE.Export    
FROM tblSpend AS TS INNER JOIN tblExport AS TE
ON TS.Country = TE.Country AND TS.Metal = TE.Metal;

[Charles_Access]

Hi Dal,

Thanks for your reply.

There were a few issues. When I run the query it asks me for the metal name, instead of giving me a data for all metals. And, when I entered "Iron" it gives me data for Iron but also has duplicates as shown below.

Country	Metal	SpMonth	Spend	ExpYear	Export
China		March	300	2001	2000
India		Jan	400	2003	5000
India		Jan	400	2002	4000
China		Jan	100	2001	2000
China		Feb	200	2001	2000

[Dal Jeanis]

That means the name of the field on the table isn't properly attributed in the query. Either the name on the table is different, or the table alias is misspelled, or something like that.

Since it's returning blanks for TE.Metal, I'd check the spelling of that one first.