Hi! I wanted to have a live currency converter on my access database. I have a table labelled 'Currencies' and In it I want a realtime conversion rate of a number of currencies in USD, it makes it easier to see the total spent - when it is in all one currency. If anyone can help me with this first step - I may be able to do the second step, but that does not matter for now.

An advanced thankyou for help.
-Larz

Hi Larz,

What version of Access are you using? Please remember to select the version number when posting questions in case it becomes relevant to the discussion.

By "live," do you mean that you want your Access database to fetch the conversion rate on the Internet?

Just curious...

Larz,

Not to rain on your parade, but to me, while Access can handle it, I think performance may suffer. What about a link to an on-line/real-time currency converter? Here's a sample - Real-time Currency Converter


AvgJoe

Hi again dbguy - I am using access 2010! As for live - yes ideally I wanted to grab it from the internet. I have managed to get conversion rates on an excel spreadsheet, but can't link it to my database, so id prefer to do it directly from access. In theory I want it to update reasonably frequently. Also if the data is present on an excel spreadsheet - and I do manage to link in to my database will I then have to keep this spreadsheet open in order to get the live updates?

Hi joe, I was hoping not to have to convert it all manually it would make it hard for me. Even if its not realtime… and one could click a button which refreshes it that would be better.
-larz

Hmm... How did you "manage" to get the conversion rates into your Excel spreadsheet? I imagine that you could do the same thing in Access.

What do you mean that you cannot link your spreadsheet to Access? You should be able to, and no, the spreadsheet does not have to be open for Access to read the data from it.

Just my 2 cents...

On excel:
data -> data from web -> www.x-rates.com
then I selected the table and it took the data from the site and put it on my spreadsheet. I can hit the refresh data button and it will refresh the data for me.
I don't know how to do the same in access although

Okay, thanks. I'm not sure how to do that in Access either. I'll see if I can find out for you.

In the meantime, how did you try to link your Access database to your Excel file?

In external data -> Excel -> then I selected the document. Although structurally it looked like wouldn't work - I can't really explain what I mean you'd have to look for yourself.
Thanks,
LArz

Hi Larz,


Yeah, not sure what you mean either but I just tried it and it worked for me. I just went through the Wizard, selected the Excel file and the data showed up in Access.

Are you getting any errors?

yeah was just going to say it works for me too. So there is no problem there I guess. I've just been working on the second step - which I really have no idea what to do. Going back to the question I asked yesterday. I want the currency at that particular time to be constant i.e. once the form has been 'closed' for it not to be changed and to remain the exchange it was when it was set to 'closed'. Do you have any ideas?
Thanks,
Larz

Hi Larz,


I'm afraid you will have to explain that a bit more. You said it yourself, when you click on the "Refresh" button in Excel, it updates the spreadsheet. And, since it's linked to the database, that means the data will also change in your table.

Are you saying now that you don't want the table to update? Then, what was the idea behind your question of keeping the rate "live?"

Also, I don't understand what you are referring to as "closed." Sorry, I don't work in the financial industry. Is that a "usual term" for the money people that I am not familiar with?

Just my 2 cents...

Sorry dbguy, by 'Closed' I was reffering to the yesterday when I was talking about setting the form to closed it could not be reviewed by another user. Your suggestion was to filter it so only the 'opened' ones could be viewed.

What my intention was, was to have a purchase on a form - if this purchase is closed it will basically be included on a report which is filtered with a query which will have all the 'closed' = (paid and received) items on it.

So basically if it is then filtered onto a report - I wanted the item's price in USD to remain constant - as it has already been paid. If it is still open (not paid) it therefore is still exposed to fluctuations within the exchange of currencies to USD.

I see the complications that arise though.

Does this make better sense. Im sorry for not being clear.

Oh and this prodject is purely academic - I just never have been exposed to access and I thought making a purchasing system would help me learnt.
Thanks,
Larz

Hi Larz,

Thanks for the clarification. To maintain an accurate record for those "closed" transactions, you'll have to store the actual USD amount paid at that time in your table.

So, for example, let's say you have an "outstanding" transaction and the conversion rate says that they should pay $20. Then, if they do pay, enter 20 into the record, and it shouldn't change at all (unless you edit it later).

Does that make sense? Basically, you will only use the conversion rate table as a reference and not as an actual record in the transaction table.

Just my 2 cents...

Just to clarify, when you have form with say 20 euros to be paid, you could then once the order has been selected to closed and you click the button to save the record. It will cross reference that value of 20 euros with the current conversion rate at that given time, to change it to USD which would then be stored as the record. If so, this is perfect!
-Larz

Hi Larz,

Yes, if that's what you want, you can have "code" run to enter the converted amount when the user marks the transaction as "paid" or "closed." Do you need help doing that?

Yes, as Im not familiar with VBA in access - it is proving more difficult than I expected. It isn't as similar to VBA in excel as I expected!
Thank you!
-Larz

Hi Larz,

Okay, no problem. So, let's say you have a combobox where the user can select "closed" and you want to store the amount paid at that point. In the AfterUpdate of the combobox, you can try this code:

If Me.ComboboxName = "closed" Then
Me.AmountPaid = Me.AmountOwed * Me.ConversionRate
End If

Of course, the above code is just a sample since I have no idea what your table field names are or where you're storing the conversion rate. But hopefully, you can get the idea from that.

Just my 2 cents...

thank you dbguy - will test that out later, and let you know if it works - thanks for all the advice!

Hi Larz,



We are happy to help. Good luck with your project.

Facing a little problem - I'm having difficulty autochanging the 'currency conversion rate'. I have a table called 'currencies' which has all the conversions. Is there an offset equivalent on access? So If I select 'EUR' it will autochange the conversionrate to the value which is present in the nextdoor field?

Sorry to rephrase that simply:
They both correspond to the same primary key. How would I work my database so that:
When the currency is selected, the conversion rate will change automatically as it has the same primary key.

Hi Larz,

I'm afraid what you're saying doesn't make sense to me. Are you able to post a zip copy of your db with test data?

So in a simple form, there is 'Currency', 'Amount' ,'ConversionRate' and 'AmountinUSD'. I have a table which is my 'master table' which contains these 4 things and an ID. I then have a Currencies table. This contains a currencyid which is the primary key. I then have 'Currency' (this is EUR/GBP/USD etc.) and conversion rate. So this means EUR, and its conversionrate has the same currencyID.

Does this make sense so far?

So then when I change the currency on the form to EUR, I want the conversionrate on the form to change automatically. Because they have the same currencyID would that be an easy way to do this?

-PS: I would send you my db but because I've been playing around with it lots, its gotten v. confusing & lost all structure - so It would be more difficult to make sense of.

- Hopefully tis makes it clear
Larz

Hi Larz,


Not sure yet but that may be the source of your problem. Normally, you wouldn't store the result of a calculation when it can be calculated on-the-fly to maintain the integrity of the data. I think you should only have the date and amount stored in your transaction table. You shouldn't need to store the conversion rate at all.



Yes, that should work automatically as long as you point to the same data source. For example, this is how I would set up the form:

Use a Combobox to select the currency. For the Row Source of the Combobox, use the currency table - make sure you include the ID and the conversion rate fields. For the calculated amount, use the conversion rate column to calculate the new value. That might look something like:

=[ComboboxName].Column(2) * [Amount]

Just my 2 cents...

dbguy, sorry if this is silly move. What I have at the moment is Currency - which source data is from currency table and it is 'CurrencyID' and also 'Currency' (eur/gbp/usd etc)
then on form is another field 'Conversionrate' (this at the moment is not the total value after conversion but the decimal value which is multiplied against the total amount value to give the US amount)

basically in your way I'm not sure how I can; Display currency to users in the form of: USD,GBP yet the field acts as this decimal in the multiplication.

Does this make sense?
Sorry if I'm overcomplecating things!
-Larz

Hi Larz,

We will probably go round and round on this because I doubt that we'll ever be on the same page without really understanding each other's position because of the lack of a sample db where we can see what each other is talking about.

Anyway, lacking a concrete example, this is what I imagine you're trying to do:

You have one table with currencies and their conversion rates. Then, you have another table with "amounts" that you want to convert into various currencies. Then, you have a form where you want to see the value of the conversion. If so, I imagine you binding the form to the amounts table and have a combobox based on the currency table to select the currency from, and a textbox with a calculated expression to display the converted amount in USD.

Now, I'm not yet sure what the purpose of this database so I can't tell you what to do with the value in the textbox after it has been converted to USD.

Just my 2 cents...

Here is an example of what i mean. Hopefully this will make clearer. So what I want to do - is when I select 'EUR' I don’t want to have to use a combo box to pick the currect conversionrate. As in the long run I am intending to remove that conversionrate from the form.
This is it very simplified and I did it very fast so there may be little errors with defining data.
-Larz

Hi Larz,

Thanks for posting the db; but like I said earlier, I don't understand the purpose of the "master" table. Calculated values are usually not stored in the table. For example, right now you have a sample data in there for USD with conversion rate of 1 and amount of 100. Just having those three values, you don't need a field for AmountInUSD. What would the purpose of that field? You can always get that value just based on the previous three fields.

Also, the reason why we recommend using a Combobox (although you are correct, you don't have to use them) is because it helps with data integrity. For example, you said that you want to maybe later on change the value from say EUR to GBP, you can be certain that there is a record for GBP. No, you won't have to select the conversion rate after that. All you have to select is the currency and the conversion rate will be automatically changed for you. If you don't use a combobox, a user could try typing in a currency that doesn't exist. Then, you'll have some data problems in your database.

See the attached modified version of your db for what I mean.

Just my 2 cents...

PS. One more thing, we also recommend that you don't use comboboxes at the table level. This could result in confusions later on.

This is exactly what a meant, so thankyou.
Although the flaws you have pointet out are indeed very true.


I was thinking 'AmountInUSD' could be used when a report is created when one was to calculate annual spendings? As for conversionrate - I see what you mean, it would make more sense if it were just a reference.

So when you are to change your currency, the amountinUSD would change because it would multiply the amountinUSD with this reference.

Is this what you mean? If so, how would you reccomend I do this?
-Thanks ver ymuch
-Larz

Hi Larz,


But doesn't the demo I posted already does that? Try changing the currency in the form and watch the AmountInUSD change every time you change the currency.

If you're asking how to make it change the value in the table, that was my question to you - why even have it in the table?

I would be able to answer your questions better if I knew the purpose of your database. Mainly, why do you feel that you need to store the value of AmountInUSD in the table at all?

Just my 2 cents...

My purpose of my database is no purpose . Its purely academic - but it is basically a Purchase Order system - as I thought it'd give me a lot of things I could learn. I thought its always best to know how much you've spent overall against your budgit. And if you have both in the same currency that’s best way to compare it. In my vision I thought this would be the best way to do it.
Your thing works - and it is exactly what I wanted. 'master' is short for masterorderlist - it just means every order is on there. What I was saying though is agreeing with you. At the moment 'ConversionRate' is a column on my master table, and it doesn't need to be - having loads of 0.7957's in my master table is not necesarie. So I still want it converted into USD, but when 'EUR' is selected it will convert it without the need of it being a column in my 'master' table.

Although dbguy what I am suggesting is not completely important. Yes it will make it structurally nicer but if what I've just said is poorly expalined and doesn't make sense to you, it does not matter. Your current suggestion works very well.
-Thanks
Larz

Hi Larz,

If you're trying to create a Purchase Order System, then the following table structure would be what I think you'll need:

tblProducts - lists the details of all the products that the customer can purchase
tblCustomers - lists the details of all your customers
tblOrders - lists all customer orders
tblOrderDetails - lists the line items for the customers purchase
tblInvoices - lists all the invoices to the customer orders
tblInvoiceDetails - lists all transactions regarding customer invoices

Now, in the tblInvoiceDetails table, you'll have to decide whether you want to store the different currency the the customer used to pay for their orders, or just store the equivalent amount they paid in USD. You don't need to store both because you can always convert one to the other whenever you need to.

If you are only doing this to learn proper database design, then I recommend that you read this Wiki article on Normalization to understand why I keep telling you that you don't need to store "calculated" values in your table.

Let me know if you have any questions or if you need us to clarify anything from the article or its concept.

Just my 2 cents...

yes yes now I understand. And I have these listed tables but my names for them arent well translated to english.

I see now things like quantity/conversionrate etc. can be added to the form and the form only and akt as values for an equation.

My master order list = tblOrders basically.

Thanks for that link - It really helps me understand access structuring to a higher level. I appreciate all your help and no doubt I'll be posting here very shortly with a nother problem I have run into hehe.
-Larz

Hi Larz,



Here's one more link if you need to learn about the basics: Cyrstal's Access Basics

Good luck with your learning!