Access query to convert currency's

Posted on 2012-09-12
Last Modified: 2012-09-13
I have a need if possible for an access query to convert currencies.
The data is a follows (this is simplified)

Column: Ref     Currency amount1 amount2 amount3
Data     :a123    Eur          100          125       85
             b765    GBP         250          75         0
             A324    sek           500          500     15000

I would like to convert all this amounts to GBP. So Im guessing that I need to somehow check the currency field, then apply a formula that converts those amounts to GBP. I was thinking that adding a table called convrates, that list all the possible currencies in the DB along with the conversation rate and then somehow link this and convert the amounts
Question by:George_Milton
    LVL 77

    Accepted Solution

    I would suggest that you do not change the original values; just show the converted values as well as the originals.

    Your idea is sound.

    A currencies table is what you want.  It must hold the currency name in the same spelling as your data.  And as you say a conversion rate.

    You have to match the conversion rate with the formula you are going to use..
    If you want to convert by multiplying ...
    GBPConverted= OriginalAmount * ConversionRate
    then you must enter into the currencies table the numbe of pounds for 1 originalCurrency unit
    So for 1 Euro you would enter something like (as as 21sept2012)  0.7994.

    You create a query using your data table and the tblCurrency and match on Currency name.  Then in the query you just create new columns for the converted values.
    For example...

    Author Comment

    That works just as I expected, thank you. Just one last question, is there any way to add a parameter that the currency if matched is asked for the conversion rate. I have been told that some of the amounts are not done at the current rate.  So for example the query is run, it matches a currency and asks for the rate.
    LVL 77

    Expert Comment

    That's not going to be possible in a query.  It would require a VBA procedure to work out which currencies to ask for and interact with the user to get those values.

    But surely the whole idea is that the rate in table is the one you should be applying?  Otherwise what is the point of the table?
    LVL 30

    Assisted Solution

    If you run the query from a form, you may check the rate and update it if required.

    A combo box holds the currencies and the current rates from convTable.
    A textbox displays the currency rate and allows for modification, and the query runs. The edited value will replace the old currency rate in the convTable.

    Author Closing Comment

    Thanks, great help
    LVL 30

    Expert Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Suggested Solutions

    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now