Currency Convertion

[Guest]

I have 2 tables:

-------------------------------------------------------------------------------------------Table 1 (Trade1):
ID Name Currency Amount USD_Equivalent
1 AA Company USD $1,000,000.00 $0.00
2 BB Company JPY $10,000,000.00 $0.00
3 ABC Insitute EUR $900,000.00 $0.00
4 ST Shop THB $34,000,000.00 $0.00



-------------------------------------------------------------------------------------------
Table2 (Currency):


ID Currency USD_Equivalent
1 USD 1
2 GBP 2.015734
3 JPY 0.008603
4 EUR 1.362034

------------------------------------------------------------------------------------------
Question:

I need to generate an SQL Query which :


Which will automatically select the right currency sign and convert that
amount into USD Equivalent.

So, it has to select the right currency sign and divide itself by
USD_Equivalent.




Thank you so much ! !

 

[Guest]

What is the relationship between the two tables

 

[John Spencer]

SELECT Trade1.*, Trade1.Amount/[Currency].USD_Equivalent as USDollars
FROM Trade1 INNER JOIN [Currency]
ON Trade1.[Currency] = [Currency].[Currency]

In the query grid (Design View)
-- Add both tables
-- Drag from currency field to currency field to set up the join
-- Select the fields you want
-- in blank field type the following
USDollars: Trade1.Amount/[Currency].USD_Equivalent

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..