Help Designing an Update Query to Convert Currencies Please (1 Viewer)

Hello all,

My apologies in advance for such a long post, but I really want to make clear exactly what my problem is, I'm sure that there is a relatively simply solution to this...

We have an incredibly old SOP system which contains data regarding orders that are required for the running of our Access DBMS. To use the data in Access we download batches of data into CSV files and then twice daily append/update these records to a table in the Access file for use with the queries in there. This all works fine and has done for some months now. However, we have now got to the point where we need to analyse some of the data in monetary terms. The problem is that monetary values in the SOP data are stored as a currency format and the actual currency is stored in a separate field of the same table, which simply stores a string denoting the currency; there are three of these: GBP, EUR and USD.

The data that is to be reported needs to be summed and displayed in GBP, and so I am looking to convert the values to GBP in the Access table that stores the SOP data, so when the reports are run it is just a matter of summing the values, rather than slowing the queries down by performing the calculation in them every time that the report is run. Reports will always be run at least a week after the previous month has ended and so my plan is to create a table which stores the conversion rates, which would then be updated manually as soon as the new rates are available (usually around the second working day of the month). This table would contain the start and end date of the month and the conversion rate for EUR and USD.

Each record in the Access table has an invoice date associated with it and so I could then run an update query (in with the existing queries that are run twice daily) that for each record finds the appropriate date range and calculates the currency value to be GBP (by looking up the currency and then finding the conversion rate for that month). The result can then be stored in a new field (which defaults to zero), and this new value can be summed on the reports.

My question is this: what is the best way to structure this update query? I'm not sure how to go about it. It almost needs to iterate through each record in the Access table and for each record loop through the date ranges in the currency table until an exit criteria of >=Start Date AND <= to End Date is met. How would I do this in a query? Or is there another way of achieving the same result in a query?

Thank you for taking the time to read this post and please offer some suggestions, as I am stuck for ideas!

Matthew

the process of converting stored data to a different currency won't be time consuming.

however, you OUGHT to storethr currency in which you need the data to be stored.

I would actually store the base amount, base currency, and conversion rate applied, as long as its a once-only value. If it changes depending on todays date, then its not the way to go. That way you dont have to keep looking up the conversion rate, but you do have the original data, which you probably will need one day!

Thanks for the reply, however, I'm still a little unsure of how you would retrieve the correct month's exchange rates. My plan was to create a new table that stored a record for each month, specifying the date range for that month and the conversion rates that applied during it. Then for the query to calculate the base unit price for each record, by multiplying the prime unit price by the exchange rate for the corresponding currency that is stored in the new table for the month that the record's date falls into. How could this be achieved in a query for every record stored in the table?

For the query to work you are best off with an exact match for the join. If you have monthly rates then you can extract the month and year from your transaction date and then join this to your rate table by holding the month and year for the rate. This will be easier to program than using the beginning and end days of the month.

I won't comment on whether this treatment is acceptable accounting practice!

Thanks Neil,

That's a good idea, it will certainly make the construction of the query easier, just thinking about it, I could re-design my rates table to have just two fields: a record name and a conversion rate. The name could consist of the last 6 characters of the date, ie "092008" concatenated with the currency string, ie "EUR" and so the name would read "092008EUR", and then the exchange range for that month's Euro could go in the next field. I could then have "092008USD" with its exchange rate too, and link the records that way (as that string name can be built from the data in the table from the SOP).

Thanks again!

Matthew

What SOP system do you have? I have a sneaky feeling that the SOP will record the base value along with the no of dec places, the conversion rate at transaction time. Your thread mentioned a default of 0 zero fo rthe conversion rate, you need to change the default to 1 as you may come across the Division by zero error. by multiplying by 1 still give the same value.

CodeMaster:

M_S_Jones said:

Thanks Neil,

That's a good idea, it will certainly make the construction of the query easier, just thinking about it, I could re-design my rates table to have just two fields: a record name and a conversion rate. The name could consist of the last 6 characters of the date, ie "092008" concatenated with the currency string, ie "EUR" and so the name would read "092008EUR", and then the exchange range for that month's Euro could go in the next field. I could then have "092008USD" with its exchange rate too, and link the records that way (as that string name can be built from the data in the table from the SOP).

Thanks again!

Matthew

Click to expand...

It's not a good idea to store data like this. You can always add data together in a query or a form but pulling it apart is more problematic. I would say you need three fields to identify the rate that you apply, the month, the year and the currency. From your transaction data you have the currency and you can use DatePart() to get the month and the year for the transaction. In a query join the rate table and the transaction data on all three fields. Robert is now your mother's brother.

DCrake said:

What SOP system do you have? I have a sneaky feeling that the SOP will record the base value along with the no of dec places, the conversion rate at transaction time. Your thread mentioned a default of 0 zero fo rthe conversion rate, you need to change the default to 1 as you may come across the Division by zero error. by multiplying by 1 still give the same value.

CodeMaster:

Click to expand...

We're undergoing an ERP at the moment and the old SOP is one of the things that is being replaced. Our newer SOP is currently dealing with some parts of the business process and the old SOP is working on others. The exchange rates aren't updated in the older SOP and haven't been for some months, this Access database is acting as a bridge between the two (although the exchange rates are dealt with entirely in the newer SOP, which when fully implemented will make this section of the Access database redundant).

The prime unit price is to be multiplied by the exchange rate, which is why I thought a zero would be ideal for those orders that are in months without an exchange rate, as a unit price of 0 could not be mistaken as being correct, whereas if the unit price were multiplied by 1 then the prime unit price would be displayed as the base unit price, which could easily be go undetected.

As for Neil's advice on the exchange rates table, what would be the disadvantage of such a naming structure? As long as all entries were entered in that naming format (which could be done using VBA behind the userform that lets the user enter the exchange rates), what would be the risks? Why would I need to pull the data apart? Surely if I needed to retrieve any of the values I'd just query them using the name in the criteria?

M_S_Jones said:

As for Neil's advice on the exchange rates table, what would be the disadvantage of such a naming structure? As long as all entries were entered in that naming format (which could be done using VBA behind the userform that lets the user enter the exchange rates), what would be the risks? Why would I need to pull the data apart? Surely if I needed to retrieve any of the values I'd just query them using the name in the criteria?

Click to expand...

It's just that you are adding complexity. The unwritten law of db development is that whatever you think or are told at the outset is a lie. Users will always want data in a different way from the specification. Holding this data in its 'natural' form is part of future proofing.