Prev: Thanks To all MVPs.. and John
Next: Split Form with need to Provide Selection List
From: stelladimare on 2 Mar 2010 11:08
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!
From: KenSheridan via AccessMonster.com on 2 Mar 2010 13:12
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/Forums.aspx/access-gettingstarted/201003/1

From: Arvin Meyer [MVP] on 2 Mar 2010 13:17
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" <stelladimare(a)discussions.microsoft.com> wrote in message
news:6F07E115-D5E5-40D5-8D85-48D043F67541(a)microsoft.com...
>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!


 | 
Pages: 1
Prev: Thanks To all MVPs.. and John
Next: Split Form with need to Provide Selection List