table linking issue: currency conversion

[bimmer5]

I have a requirement to build a query based on three sales tables. The challenge is that the "product pricing" and "rebate" fields are in their native currencies (Canadian dollars, US dollars and Euro). The US entries are flagged with the "U" values, the European entries are flagged with an "E" value while the Canadian entries are not flagged at all - they are left blank in the "Currency Type" field.
The requirements are:
1. To build a new "converted to Canadian$ price" and "converted to Canadian$ rebate" fields in the query for all US and Euro entries
2. To copy the Canadian values to these new fields as is
3. Use the DAILY currency exchange conversion rates from the "Currency Exchange History" table.

My challenge is to properly link the three tables that can get me the results above. Please find attached the tables and the query.
Thank you for any suggestions.

 

[Pat Hartman]

Convert the currency in the query. Don't store the conveded value. You'll need to update the table on a daily basis to keep the converted values current.

 

[bimmer5]

Ted,
Thank you very much for your reply.

Ted, indeed my intention is to convert the currency fields in the query and not directly in the tables. The three tables are linked with the accounting database and will be updated daily, that is not the issue. My problem is linking the “date” and “currency type” fields. Whichever way I tried to link them, I keep getting weird results. Please find attached an example what I need to get at the end of the day:

The first line above is in Canadian currency (see blank “currency type” field). As a result, the converted fields show the same amounts. The second line is in Euro currency and 1.6 multiplies the “price” and “rebate” fields. The third line is in the US currency and 0.98 multiplies the “price” and “rebate” fields.

Can you use the sample attached and link the tables so that I can get the results like in this example?
Thank you again,
Peter

 

[GaryPanic]

thats not much cop...

ok - I have used various currencies

what we used was a floating exchange rate

so when an order came in we looked at the exhnage rate at that time/day (as some days we did not need to change due to no orders in italian lira (be thankfull you never had to use this £ to lira was 1000,s


anyway we have a drop down for currency (based on a table)
so sterling£ then an extra field with the exchange rate 1.00 (auto populated from the table- but overwritable- but this would be up to you
and our invoicing system recorded a date on the invoice and this exchange rate and currecy type all in the history table

we had over 20 currencies and this worked fine