I have an ORDERS table that holds order information along with an EXPORT flag. The CUSTOMERS table has an exchange rate reference within.

I also have an EXCHANGE RATES Table (updated monthly, sometimes twice a month), that holds currency exchange rate information.

I need to report on the orders,customers table and match the appropriate exchange rate to the ORDER item.

Presently, I have a query that retrieves all orders received for the month in question - how can I determine the actual exchange rate from the rates table in one neat/ concise query? Will I need two/three queries?

I will want to report on a particular month to see all orders that have been received. So, I will select say April on my form - the code will need to retrieve a subset of exchange rate info for April 2008 (if there is more than one rate entry for the same currency I will use the higher value), and then use these rates for the top level query to match to the corresponding order items.....

Any good ideas on how to do this cleanly, will be received with open arms!


Thank you.
:p

When using exchange rates, Vat rates, discount rates, etc you need to store the actual value on the date of the transaction. So when you later run a statement based on historic data it matches exactly to the value of all the invoices added up together. Although what you are attempting to do is to reduce the amount of columns required you will not get a 100% accuracy rate by referring to look up tables. You will find alll accountancy packages do this.

You only need to be a few pence/cents out on one invoice for the customer to query it. It's called 'reconcilation'. You are ok in storing the current values in you seperate tables and maintaining them periodically. but the actual value at the point of sale must be captured.

Lets say your customer enjoys a 10% discount on all orders upto the 1 Jan 2008, then that value was increased to 12% the next time you run off a statement for this customer you balance may be at least 2% out. If it is in the customers favour I doubt he will complain, however when the company accountant attempts to reconcile the payment against specific invoices they will not match. And guess who will get the blame.

CodeMaster:

The problem with this solution is that the value at the point the order is received may be different come invoicing time seeing as jobs as generally invoiced some 2 months after the order is received. The report is only a statistical 'ballpark' figure anyway - so no specific accuracy is required at this time.

I have got around the problem - a little less neat than I would have liked(!) - I retrieve a table containing the exhange rates for the period I am interested in - then I 'DSum' all export values as I loop through the recordset of rates - totalling the sum value as I go.

Seems to work OK!

Thank you for your comment though.

Upload sample i will give you a simple and neat solution but in 2003 format

Khawar