Looking up currency conversion according to date

[littlespoon]

Hi,

This query is driving me insane and I'd love some advice.

So. I have a column giving a bunch of revenue values in different currencies, and I want to convert them all into GBP. These transactions are also over a period of time, so I need to convert them using the time-appropriate exchange rate.

In my table "Sales Data" I have a column 'Currency' which gives the currency, a column 'Transaction Date' which gives the date of transaction, and a column 'Revenue' which gives the value.

The currency lookup table "Currency Lookup" is arranged with months along the side and the different currency exchange rates in labelled columns. Each month is formatted as "01/mm/yyyy".

I want to say, for example:

Iif([Currency]='USD', [Revenue]/***The exchange rate given for USD in the month of this record's [Transaction date]***,"")

Also, I have ten different currencies to do this for, so rather than nest the Iif functions I'd like to have a series of functions, each checking for a different currency, and then to sum them at the end to one field 'Revenue'. How do I do this?

Also, I have a very limited understanding of SQL and work mostly in Design View; if someone can explain this to me using that I would be very grateful!

 

[spikepl]

The currency lookup table "Currency Lookup" is arranged with months along the side and the different currency exchange rates in columns. Each month is given as "01/mm/yyyy".

You are already off of the beaten track because you use data as column names. That makes everything much harder and contravenes "data normalization" - google that term and assimilate the concept. There is no normalization police though, just huge self-inflicted PITA forever as penalty.

You currency table should be

tblRates
---------
RateID (PK, autonumber)
CurrencyCode (currency abbreviation)
CurrencyValue (the actual value)
CurrencyDate (date from which valid)

 

[littlespoon]

There are ten different currencies; does this mean I need ten tables?

And each data point is valid for one month e.g. January 2015. What implications does this mean for the "CurrencyFrom" field you suggest? How do I format this?

 

[Minty]

Nope you don't need ten tables - create the table as suggested and you will see that there is a column that defines the currency and the period.
So you data would look like;

RateID - Currency - Value - ValueDate
1001 - £ - 0.83 - 1/1/2015
1002 - $ - 1.52 - 1/1/2015
1003 - € - 1.15 - 1/1/2015
etc...

 

[littlespoon]

Ahh. That sounds good. Let me re-do my table and get back to you if I still can't figure it out.

 

[littlespoon]

Okay, I have no clue how to proceed...

As before, I want to say:

Iif([Currency]='USD', [Revenue]/***The exchange rate given for USD in the month of this record's [Transaction date]***,"")

Only now I need the string surrounded by asterisks above to:

• look down my currency lookup table to the correct month (even if the date is 25/01/2015, I want it to look at the 01/01/2015 entry),
• pick the row with "USD" in the currency column for that date, and
• pull out the actual rate.

Please may someone explain how to do this? Thank you!

 

[Minty]

Oh and my bad - avoid using Currency as a field name - it's a reserved word...

 

[littlespoon]

Haha, I've already noticed that and changed it to "Currency Type"

 

[Minty]

I would also remove the space - long term it just makes life easier.
Break this down into small steps and it should all be straight forward.

You need to identify the date to look up first - so something like;
Month(TransactionDate) = Month(CurrencyDate) AND Year(TransactionDate) = Year(CurrencyDate).

Then match your CurrencyType ;
CurrencyType = CurrencyCode

 

[littlespoon]

I'm sorry for being slow, but where do I put those conditions?

I was thinking of pulling the rate field out by using a GROUP BY function, where I specify all the conditions that you mention in the Criteria field linked by ANDs -- would that work or is there an easier way?

And do I need to do any JOINs between the Currency lookup field and my data table? If so where?

 

[Minty]

If you Join the CurrencyCode in your value table to the CurrencyCode field in your transaction you won't need to filter those at all, you will simply need to match the date.

So create the Join on the currency and then add the transaction date twice to your query window as

dTranMonth : Month([TransactionDate)
dTranYear : Year(([TransactionDate)

And in your criteria

Month([CurrencyDate])
Year([CurrencyDate])

Should about get you there.

 

[GaryPanic]

This seems an odd way of doing this
- i would do this as follows
extra field at the end of the transaction table (after currencytype) and have ExchangeRateUsed
then just run an update once a month on the date range and per currency..
and bobs your uncle -

 

[littlespoon]

That worked! ... almost.

The problem is that now any duplicate values in my source table (ie a sale made of the same product, on the same store, from the same country) are being removed, so only one is showing. I should have about 100,000 records, but when I run this query it goes down to about 80,000. How do I fix this problem?

This is what my query looks like:

 

[Minty]

Firstly I would join the currency type fields - it should speed up the operation considerably.

Secondly your problem is caused by you grouping the transactions. I'm not sure that the grouping is actually achieving for you if you need to update every record simply remove the grouping completely.

 

[littlespoon]

What do I put instead of 'Group By'?

 

[littlespoon]

I changed everything to EXPRESSION, and it worked!! Thank you so much everyone!!

 

[Minty]

In your query designer there is a icon/button with totals remove that and you'll get a normal select query.