joining two tables

[santosh1]

Hello -

I want to get necessary info from two tables and put it into a separate table. Please let me know how I can do this.

I have 2 tables, Temp and Geography. Table Temp has field countryID. Geography Table has fields countryID, CurrencyCode and
CurrencyDescription. What I want to do is, for all the countries that are in the Temp table, I want to get respective CurrencyCode and CurrencyDescription by looking at the Geography Table. Then, put this onto a separate new table Temp_Currency which will have CountryID, CurrencyCode and CurrencyDescription.

For example,
if countryID is 001 in the Temp table, I want to look at countryID 001 in Geography table, and find the currency Code (USD) and description (US Dollar). The temp_Currency table should have countryID 001, CurrencyCode USD and CurrencyDescription US Dollar.

I will appreciate your help very much.

Santosh

 

[adrikev]

Hi santosh1,

What you want to do is set up a make table query that writes all of the data from the Geography table where the countryID is equal to the countryID in the Temp table. Here is a piece of SQL you can use to set up the make table query:
[red]

SELECT Geography.countryID, Geography.CurrencyCode, Geography.CurrencyDescription INTO Temp_Currency
FROM Geography INNER JOIN Temp ON Geography.countryID = Temp.countryID;

[/red]
You can paste this into the SQL view of a new query and flip it over to design view and save it. Whala, there is your make table query. It will make a table called Temp_Currency.

Let me know if you need further help with this. --------------------------------
Fortius, Citius, nullis secundus
Strength, Speed, second to none

 

[santosh1]

Hello,

Thankyou very much for your help.

I just realized that the currency description is not in the
Geography table but in another table called Currency which has currencyCode and currencyDescription fields.
So, that means, I have to look at the Geography table for the currencyCode for that particular countryID and for that particular currencyCode, I need to refer to Currency table to find out the currency description.

Would you please let me know how I can do this?

Santosh

 

[adrikev]

santosh1,

From what I understand these are the fields you have for your three tables:

-Temp has countryID
-Geography has countryID, currencyCode
-Currency has currencyCode, currencyDescription

If this right? If it is then your query would look like this:
[red]

SELECT Temp.countryID, Geography.currencyCode, Currency.currencyDescription INTO temp_Currency
FROM ([Currency] INNER JOIN Geography ON Currency.currencyCode = Geography.currencyCode) INNER JOIN Temp ON Geography.countryID = Temp.countryID;

[/red]
This makes a table (temp_Currency) that has the following columns:
countryID --> from the Temp table
currencyCode --> from the Geography table
currencyDescription --> from the Currency table

Let me know if this works for you. --------------------------------
Fortius, Citius, nullis secundus
Strength, Speed, second to none

 

[santosh1]

Yes, it works! Thanks for your help very much.