Access query to convert currency's

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
Who is Participating?
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...
George_MiltonAuthor Commented:
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.
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?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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.
George_MiltonAuthor Commented:
Thanks, great help
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.