One database, multiple currencies?

I have an existing orders database that was created here in the UK and
is designed to accommodate UK Sterling. The customer will now be
trading in the US and in Europe and wants the database to accommodate
these two currencies, so that all UK, US and EU orders are in stored
in the same database.

My approach to this would be simply to convert all of my existing
currency fields to doubles. The user will be able to define the
currency type of each order via a new combo box called
"CurrencyType" (options: Dollars, Pounds, Euros). This will allow
invoices and reports to show the correct currency symbol where
appropriate.

Is this too simplistic? Will become unstuck using doubles instead of
currency? Will existing data convert easily (in one step)?

One of the main reports that the database currently does, is to strip
VAT out of each product's retail price so as to display the NET, VAT
and Gross separately. Is the accuracy of the currency and double data
types similar/compatible?

Thanks

Paul

Re: One database, multiple currencies?

On Thu, 27 Dec 2007 06:17:02 -0800 (PST), Paul H
wrote:

Currency is still the right data type. Remember that you see a Pound
symbol only because of how the data is formatted for display. You can
easily change that (e.g. using the Format function).

If you have management reports showing for example total sales over a
period of time, you may also need conversion rates, unless it's OK to
say "we sold USD 1000 + EUR 2000 + GBP 3000".

-Tom.



>I have an existing orders database that was created here in the UK and
>is designed to accommodate UK Sterling. The customer will now be
>trading in the US and in Europe and wants the database to accommodate
>these two currencies, so that all UK, US and EU orders are in stored
>in the same database.
>
>My approach to this would be simply to convert all of my existing
>currency fields to doubles. The user will be able to define the
>currency type of each order via a new combo box called
>"CurrencyType" (options: Dollars, Pounds, Euros). This will allow
>invoices and reports to show the correct currency symbol where
>appropriate.
>
>Is this too simplistic? Will become unstuck using doubles instead of
>currency? Will existing data convert easily (in one step)?
>
>One of the main reports that the database currently does, is to strip
>VAT out of each product's retail price so as to display the NET, VAT
>and Gross separately. Is the accuracy of the currency and double data
>types similar/compatible?
>
>Thanks
>
>Paul

Re: One database, multiple currencies?

Why do you want to change the data type? If Currency works for you for
Sterling, then it will also work for Euros and US$. You just need to make
sure that text boxes are appropriately formatted on screens and reports
(using the Format property) so that they don't display the £ symbol.

Your CurrencyType field is the usual way of identifying currencies in a
multi-currency system. One of the most important things to watch out for is
that you never allow currencies to be mixed inappropriately: for example, if
you have an invoice in Sterling, but you receive payment in Euros, then the
payment must be converted to Sterling at an appropriate exchange rate before
it can be allocated to the invoice.

"Paul H" wrote in message
news:805fbd2c-8cba-4f99-a908-661d239a1a83@e23g2000prf.google groups.com...
>I have an existing orders database that was created here in the UK and
> is designed to accommodate UK Sterling. The customer will now be
> trading in the US and in Europe and wants the database to accommodate
> these two currencies, so that all UK, US and EU orders are in stored
> in the same database.
>
> My approach to this would be simply to convert all of my existing
> currency fields to doubles. The user will be able to define the
> currency type of each order via a new combo box called
> "CurrencyType" (options: Dollars, Pounds, Euros). This will allow
> invoices and reports to show the correct currency symbol where
> appropriate.
>
> Is this too simplistic? Will become unstuck using doubles instead of
> currency? Will existing data convert easily (in one step)?
>
> One of the main reports that the database currently does, is to strip
> VAT out of each product's retail price so as to display the NET, VAT
> and Gross separately. Is the accuracy of the currency and double data
> types similar/compatible?
>
> Thanks
>
> Paul