I have built an accounting application that deals with foreign currency. The main query that converts local currency ammounts into USD has the following sql statement to make the conversion:
Rev_LCY*(select rate from tbl_Exchange where tbl_Products.currency=tbl_Exchange.currency and tbl_Exchange.year=Tbl_Monthly.year and tbl_exchange.month=tbl_Monthly.month ) AS Rev_USD
tbl_Products is where the currency code is stored i.e. GBP, EUR, USD... for each product.
tbl_Exchange has monthly exchange rates for 10+ currencies.
Is there a better way to convert than using a select query inside another select query? My application works right now, but I am limited in the way I can query the Rev_USD calculation in later queries.
first off I suspect it depends on what you are looking to do with the converted cash data.
If its to reconcile a purchase for instance for that time frame from "Other" currency then you can you a simpel macro or code to take foreign cost and save to USD. or save both in table so that you keep historical cash flow (sometimes very usefull)
I have all exchange rates in a table - these are called by a query to a sub form. This is used to convert a parent forms textbox Ive named COST.
This way what ever I transact in I call that country and the exchange, in my cast Pound Sterling that gives me a chance of using a simple setvalue macro and a command to save that converted rate against the product. - keeping the historical original foreign charge value can help to sort any issues at a later stage
I am more or less looking for a better way of converting the month of cash from local currency to USD. This USD ammount is is used in many other queries and reports throughout the application, and currently is not 'stored' anywhere (its all in a calculation query).
not sure that storing a single forec rate for a day will work, some currencies are fixed for a day / week / month, some a floating. So I'd suggest you either store the forex rate for that transaction or record timestamps for the transaction(s) OR store the forex value as well as the local value. If data storage requirements are not significant then usually I'd go down the second route.
The main reason: it simplifies reporting, its a straightforward view of the data (it makes deploying applications easier to users (they don't have to use a SQL join to rerieve the forex value). It gives them less scope to introduce rounding errors in their reports. its amazing how many times you can chase down errors in conversions. the sum of (localval*forex) rate isn't necccesarily the same as sum(localval)*forex. To some beancounters the rounding errors are significant.
Is there some sort of code that i can use for when I create a record, to pull the correct forex rate into a field in the table that holds the record. That way, I can just perform a simple query rather than a join query, as you suggested?
I do not no much visual basic or sql, so anything help is much appreciated.