Convert Currency

[wdverner]

Hi All,
I have a table that deals with selling prices of products in multiple currencies. However I want to generate a query that will return to me the cist of each product in GBP.

Products
ProductID
SellingPrice
CustomerID

Customer
CustomerID
Currency


Thus is I have ProductA at a SellingPrice for Customer TEKTIPS at 10.00, and TEKTIPS Currency is GBP, then the PriceInGBP=10.00

ProductB sells at 20.00 for Customer ABC at ABC Currency is EURO, therefore the PriceInGBP=13.996

The exchange rate in this example assumes 1EURO = 0.6998GBP.

But i ONLY want a calculation done IF the Customers CURRENCY is equal to ANYTHING BUT GBP.

Is this possible?

I hope I have explained it in as much detail as poss,
thanks for your help guys

 

[PaultheS]

Sure. Make a new table with:

Currency
CurrencyinGBP

Then in the query, join the Currency fields and make one of your query fields:

PriceinGBP: [CurrencyinGPB] * [SellingPrice]

In the new table, your entries would be like:

Currency CurrencyinGBP
EURO 0.6998
GBP 1
USD ...

 

[wdverner]

SP STG: IIf([Currency]="EUR",[Selling Price]*0.6998)