Solved

HOW TO CALCULATE EXCHANGE RATES IN MICROSOFT ACCESS

Posted on 2009-05-14
10
1,183 Views
Last Modified: 2013-11-29
Hi,
Let me preface my query by saying I am an MS ACCESS Novice.  I see that most people provide just sql code for answers on here.  I need to understand my solution conceptually first so I would appreciate a very laymen's explanation.

I have a database which tracks costs across several countries.  These costs are inputted by the users in local currency, so British Pounds, Polish Zloty, Danish Krone, etc...

I need to convert these costs into Euros.  I am aware the solution involves creating a table with the Euro/Local exchange rates and querying the data entry table with the exchange rate table to get the conversion.  I have my table with exchange rates but cannot get further than that.

All help is very much appreciated.

Regards,

MIchael
0
Comment
Question by:mcolligan1
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 24390159
How do you identify the 'local' currency?
Is it a field in the cost record?
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 24390181
Firstly, ensure you data structure is appropriate.

I would expect that the table where costs are stores will have the cost in local currency and a field that indicates what currency that cost is

The Currency table needs to have that same currency indicator field, and a value for that currency of 1 and a coilumn to indicate what Euro value 1 of the cost currency is equal to.

You have not indicated how you will deal with fluctuating currency rates.

Assuming we ignore the fluctuations, yo can now create a query that uses both tables and links the two on the currency indicator
Create a field in that query that is Costs*EuroValue - this is the value in Euros. Add in any other fields that you want.

This is the real simple start - you then need to work out what you want to do with this result.


Kelvin
0
 

Author Comment

by:mcolligan1
ID: 24390286
Hi
The costs are directly input in a field in my Cost Table.  

The only indicator I have included determining local currency is another field called Country, i.e., UK, Denmark, etc...My currency table has same indicator field.  

I have dealt with fluctuating forex by linking an excel sheet using the Microsoft Live FOREX feed function.  So, before I open the access database I have to update the excel sheet.  

This is all I need as I am calculating costs / per square foot.  It's  benchmarking database that provides me with various costs across EU, i.e, janitorial, maintenance, security, etc...
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 500 total points
ID: 24390312
Ok, create the query as per my post above, linking country in costs and currency tables


Kelvin
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24390353
Currency exchange is an extremely dynamic set of variables.  Less than an hour after you start using your table, an exchange rate somewhere can change by a percent or more - which means it is obsolete shortly after you start using it.  That said, I wondering why someone on the west coast wants to convert international currencies into EUR and not USD or CAD - school work perhaps?

http://www.ratesfx.com/rates/rate-converter.html  -  provides an up-to-date currency conversion tool.  From that source at this time 1 Danish Krone or DKK = 0.13 EUR.  What don't you under stand about how many Euro is 19.95 DKK?  
0
 

Author Comment

by:mcolligan1
ID: 24390380
GRAYL,

Not sure what the intention of your post was (?).  I know currencies fluctuate as I have to develop outsourced solutions across EMEA.  The purpose of the database is to allow costs to be input in local, convert to euro and provide euro/psm benchmarking.

Regards,

Michael
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 24390521
I'm in LA (lower Alabama) and have tons of EMEA and SAP (South Asia Pacific) currency conversions going on and its not all to/from USD. We deal in 28 currencies.

I think that GrayL thought that this possibly was a school project
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24390687
But I do know LA is not lower Alabama ;-)
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 24391137
Everybody jokingly says its LA (meaning lower Alabama) but its actually upper Alabama, 75 miles south of Chattanooga TN...
0
 

Author Comment

by:mcolligan1
ID: 24452016
When you work with Real Estate square feet and USD are the accepted standards of working, hence, the USD.

Mike
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to use DLookup with IsNull Function 4 26
DCount Type Mismatch 2 22
aggregate query? 20 53
Using Third Party DLL with Access VBA 14 17
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question