Currency storage

[Isskint]

Hey guys,

I am pulling together a database to control invoices and proformas and the payment of. These will come in different currencies and non GBP will be paid in that currency via a trading site (best rate!). I need to record the original value and the GBP after exchange.

I have hit a snag with the field for the invoice value (the GBP value will have a separate field). Access will not let you store different currencies in the same field. Various users (in different locations) will import this data via a query, into accountancy software packages (EG Sage) so the actual currency must be included.

1. If i store the number value as a double data type (eg without currency symbol) in one field and the currency in another (would be a FK to suppliers table eg EUR) how would i produce a query summing the different currencies? My original thought was simply to include the currency type field and group on it, but obviously this will not work with the accountancy software packages.
2. I considered createing a custom function to 'convert' the number to it's relevant currency, but again the query field will only accept 1 currency type.
3. If i knew how many currencies, i could do 1 field for each!!!! But not a good idea.

Any ideas?

 

[Galaxiom]

The Invoice table needs a CurrencyID field. A related CurrencyRates table stores the daily conversion rates.

The query joins the main table to the currency table on InvoiceDate and CurrencyID so the value in the total in the base currency can be calculated for any day as required.

The currency data should be stored in Currency field types in both tables. This is precise to four decimal places.

 

[Isskint]

Thank you Galaxiom.

I get the first bit, storing currency daily currency rates.
I get the second part in that you can return the GBP value (Invoice value / rate)
What currency would i store the data in the third bit? If i store as GBP £ i can only return total GBP values in queries/reports. If i store as EUR € American, Russian, Japanese etc clients will not be able to get an "in currency" import value.

If you could look at the attached shot of the current spreadsheet, i would need to be able to produce queries/reports showing summary values of €10651.00 | £16354.00 | $7217.00 but I do not see how I can achieve this.