If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Can access work with multiple FX rates?
I have to produce a monthly report that uses employees data. The yearly
salary will be in local currency and I will need to create a query (and a report) to divide the salary by 12 so I get the monthly gross salary and each month I will need that monthly figure to be calculated in USD. But the FX will change on a monthly basis (but the past months will stay the same). can access do that if I have a table with the FX rates by market and month? Every month I will update the FX rates and will need the monthly figures to re-calculate. THANK YOU! |
#2
|
|||
|
|||
Can access work with multiple FX rates?
You'll need an ExchangeRates table with columns such as LocalCurrency,
RateYear, RateMonth, USDRate. You can then include this table in your report's underlying query, joining it on the LocalCurrency, RateYear and RateMonth columns to whatever column or columns in the appropriate table underlying the reports query, e.g. the JOIN might be on a TransactionDate column in a Transactions table like so: ON YEAR(Transactions.TransactionDate) = ExchangeRates.RateYear AND MONTH(Transactions.TransactionDate) = ExchangeRates.RateMonth AND Transactions.LocalCurrency = ExchangeRates.LocalCurrency You can then return the monthly salary in USD in a computed column in the query or a computed control in the report with: (Employees.Salary/12) * ExchangeRates.USDRate e.g. as of today, the monthly salary for someone earning 30,000 GBP pa would be (30000/12)*1.4978 which is: 3,744.50 USD Ken Sheridan Stafford, England stelladimare wrote: I have to produce a monthly report that uses employees data. The yearly salary will be in local currency and I will need to create a query (and a report) to divide the salary by 12 so I get the monthly gross salary and each month I will need that monthly figure to be calculated in USD. But the FX will change on a monthly basis (but the past months will stay the same). can access do that if I have a table with the FX rates by market and month? Every month I will update the FX rates and will need the monthly figures to re-calculate. THANK YOU! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201003/1 |
#3
|
|||
|
|||
Can access work with multiple FX rates?
There are 2 methods of handling history. The preferred method is to store
the elements of the historical calculation and recalculate in a query. The other method is to store the results of the calculation and subsequently query those. Storing the elements also allows other calculations based upon those elements and more closely follows database relational set theory and normalization rules. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "stelladimare" wrote in message ... I have to produce a monthly report that uses employees data. The yearly salary will be in local currency and I will need to create a query (and a report) to divide the salary by 12 so I get the monthly gross salary and each month I will need that monthly figure to be calculated in USD. But the FX will change on a monthly basis (but the past months will stay the same). can access do that if I have a table with the FX rates by market and month? Every month I will update the FX rates and will need the monthly figures to re-calculate. THANK YOU! |
Thread Tools | |
Display Modes | |
|
|