I was wondering if anyone can help with with a problem I have with my database. It's holds cost data including purchase made in foreign currencies which need to be converted to GBP using the correct exchange rate so a variety of reporting & stats can be performed.
I have a table called Costs within which there are 2 fields
Purchase Currency
Exchange Rate
I also have another table called Exchange Rates 13/14 within which there are 2 fields
Currency
Exchange Rate
When a value is entered in the Purchase Currency field on the Costs table (this is a look up field linked to Exchange Rates 13/14 so it shows the listed currency in drop down) I need the database to automatically populate the Exchange Rate column in Costs i.e. match the value in the Purchase Currency field to the Currency field in Exchange Rate 13/14 and populate with corresponding Exchange Rate from Exchange Rate 13/14.
I have tried the following and none work:
SQL Tried
1.
UPDATE Costs
SET ExchangeRate = [Exchange Rates 13/14].[Exchange Rate]
WHERE Costs.[Purchase Currency] LIKE [Exchange Rates 13/14].Currency
2.
UPDATE
Costs
SET
ExchangeRate = [Exchange Rate]
FROM
[Exchange Rates 13/14]
INNER JOIN
[Exchange Rates 13/14]
ON
Costs.[Purchase Currency] = [Exchange Rates 13/14].Currency
;
3.
SELECT [Exchange Rate]
FROM [Exchange Rates 13/14]
WHERE Costs.[Purchase Currency] LIKE [Exchange Rates 13/14].Currency
;
I know it is possible to have a drop down for Purchase Currency which shows 2 columns (both Currency and Exchange Rate) – you can then use the exchange rate figure for a calculated field. The problem I have is that I am importing data into the costs table from excel. In Excel I can only have 1 value in the Purchase Currency column on the upload template. If I just have Euro in this column the database does not match it to the Euro in the Purchase Currency drop down and also store the correct exchange rate. The exchange rate is used to calculate a GBP Unit Cost which is used for other calculations and therefore several others errors occur. I will be uploading quite large amounts of data so I really need to fix this.
Or is the alternative to put this into the calculation of GBP Unit Cost – where this somehow matches the Purchase Currency in the Costs table to the Currency field in Exchange Rates 13/14 tables and uses the appropriate exchange rate from Exchange Rates 13/14 to calculate GBP Unit Cost in Costs table.
If anyone can assist at all I’d be very grateful – this seems like it should be simple but it is not (well for me anyway).
Thank you!
I have a table called Costs within which there are 2 fields
Purchase Currency
Exchange Rate
I also have another table called Exchange Rates 13/14 within which there are 2 fields
Currency
Exchange Rate
When a value is entered in the Purchase Currency field on the Costs table (this is a look up field linked to Exchange Rates 13/14 so it shows the listed currency in drop down) I need the database to automatically populate the Exchange Rate column in Costs i.e. match the value in the Purchase Currency field to the Currency field in Exchange Rate 13/14 and populate with corresponding Exchange Rate from Exchange Rate 13/14.
I have tried the following and none work:
SQL Tried
1.
UPDATE Costs
SET ExchangeRate = [Exchange Rates 13/14].[Exchange Rate]
WHERE Costs.[Purchase Currency] LIKE [Exchange Rates 13/14].Currency
2.
UPDATE
Costs
SET
ExchangeRate = [Exchange Rate]
FROM
[Exchange Rates 13/14]
INNER JOIN
[Exchange Rates 13/14]
ON
Costs.[Purchase Currency] = [Exchange Rates 13/14].Currency
;
3.
SELECT [Exchange Rate]
FROM [Exchange Rates 13/14]
WHERE Costs.[Purchase Currency] LIKE [Exchange Rates 13/14].Currency
;
I know it is possible to have a drop down for Purchase Currency which shows 2 columns (both Currency and Exchange Rate) – you can then use the exchange rate figure for a calculated field. The problem I have is that I am importing data into the costs table from excel. In Excel I can only have 1 value in the Purchase Currency column on the upload template. If I just have Euro in this column the database does not match it to the Euro in the Purchase Currency drop down and also store the correct exchange rate. The exchange rate is used to calculate a GBP Unit Cost which is used for other calculations and therefore several others errors occur. I will be uploading quite large amounts of data so I really need to fix this.
Or is the alternative to put this into the calculation of GBP Unit Cost – where this somehow matches the Purchase Currency in the Costs table to the Currency field in Exchange Rates 13/14 tables and uses the appropriate exchange rate from Exchange Rates 13/14 to calculate GBP Unit Cost in Costs table.
If anyone can assist at all I’d be very grateful – this seems like it should be simple but it is not (well for me anyway).
Thank you!
