Help with query

[eerich]

Hello

I've run into trouble with a query designed to perform a currency translation from foreign to USD. Basically I have currency rate table which lists: base curr; reporting curr; exchange rate.

My main table has all of the transaction detail including the currency of the specific transaction. What I need to do is translate all items from a company's base currency to USD. The transaction currency should be ignored. I've written the following query where the problem seems to be it is cycling through all of the currencies in the rate table vs. just selecting the one base currency and period and applying the rate. In essence I need the query to just verify the base currency of the company and apply the rate to the transaction without regard to the transaction table's currency (GLCRCD) or CurrX table's reporting currency (ReportingCurr). Any help with what might be wrong will be appreciated.

SELECT Round(IIf(Company.BaseCurr<>"USD",(Corp.GLAA/CurrX.CurrRate),GLAA),2) AS USD2Amt, GLAA, GLCRCD, Company.BaseCurr, CurrRate, GLPN as AcctPeriod

FROM (Corp INNER JOIN Company ON Corp.GLCO=Company.CoCode) INNER JOIN CurrX ON Company.BaseCurr=CurrX.BaseCurr

WHERE corp.Glpn=CurrX.Period;

CURRENCY TABLE INFO:
BaseCurr, ReportingCurr, XchangeRate, Period
HKD, USD, 5555, 01
HKD, GBP, 2742, 01
IDR, USD, 8954, 01
CAD, USD, 1.45, 01
IDR, CAD, 9833, 02
CAD, USD, 1.45, 02

CORP TABLE INFO:
GLCO, GLAA, GLCRCD, GLPN
100, 75000, HKD, 01
200, 275500, USD, 01
300, 543875, CAD, 01

COMPANY TABLE INFO:
Company, BaseCurr
100, IDR
200, IDR
300, HKD

 

[Remou]

The fields in your query do not match the names of those in the sample data.

If the data shown is real, you have two of each type of BaseCurr in the Currency table, which means that you will get two rows for each company when you join Company BaseCurr to Currency BaseCurr.