We help IT Professionals succeed at work.
Get Started

Building in Currency Impact (Operational Change) to Access Query

Adam Ehrenworth
Last Modified: 2012-05-11
I am going to try to explain my current process for compiling travel spend for my organization globally and see if there is an easier way to consolidate this process from using both Access and Excel to only needing to use Acces as a one stop shop.

Right now we have monthly detail that is summary level and it is appended each month to an existing database. Thre are fileds for country (ISO number), company number, ledger date, expense code, spend, count, and a final field called "month2" that is blank to start but filled in using a update query in Access.

The bottomline is I currently use both Access and then Excel to go beyond just a net month to month and year or year change for each company on the extract.

I used Access to consolidate both the Raw currency totals and also the USD totals using a master table of all country and months and the currency rates.

The GL date is covered into a GL month based on the universal calendar using a update query like I mentioned.

I take the results of this file and end up with a 2 tabs on my excel sheet that has country, company number, spend total (raw tab 1 and USD tab 2).

The process to get to those two tabs is to create a sheet that has the month to date value and then a total of the months that make up YTD for if it was April I have to use a macro in excel to creat a sheet that hs the total of Jan Feb Mar and Apr for 2010 and 2011. Along side with the current month total Apr 2010 and Apri 2011 to do my MOM and YOY comparisons.

The excel sheet is set up with tabs for every country so that I can figure out an operational change dollar amount using the following formula.

(Current Period Spend Total - Previous Period Spend Total)*Previous Period Currency Rate

This by expense type as part of the raw file.

The problem is I do seperate calculation for the month to date based on the previous period rate and then the YTD is a blended average of the previous years months so it would be the currency rate blended average for January through April 2010 in this example that you multiple the YTD total by.

All of the Op change dollars are added and then used to do the following calculation to get an operational change percentage to compare the Net change to get the currency impact due to currency flucuation over the time period.

I know this is a lot of information here but I have made some attempts and housing this whole process in Access with very little success.

If any more details are neded or you want to see a mock up of a file let me know.
Watch Question
This problem has been solved!
Unlock 1 Answer and 11 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE