automatic currency conversion

[skrebsky]

I have a simple table "jobs", basically a list of finished jobs, for which customers pay in various currencies. So there is a field "price" and a field "currency", linked to a table "currencies", to specify the currency of the invoiced price.


I would like to have another field in the "jobs" table where price is automatically converted to GBP according to exchange rates specified in the "currencies" table.

Probably, price in the "job" table has to be multiplied by a particular exchange rate, depending on which original currency it is in, and the result written into a new field, named "converted price" or sth like that.

I reckon this can be done through a query and a calculated field added to the "jobs" table, but I don't know how.
thanks for any ideas.
Jakub

[ajetrumpet]

you should not be doing this in tables. what you should is have one table with the lookup rates and one table with the target to be converted. then, on forms and the like do the calcs and lookups with functions and code. that is the professional way to do it, and will always be recommended by people. Unless of course you get advice from someone that doesnt know what they're talking about...

[RuralGuy]

...or for historical reasons since the rate may/will change.

[ajetrumpet]

...or for historical reasons since the rate may/will change.

yeah...if there was a connection string for pages like xe.com, that would be the option for this guy. Or a permanent phone line to a nyc day trader.