I have some Access queries that I am modifying to allow to report in more currencies. The user selects the currency to report in.
I have an ExchangeRates table that holds rates per currency per month against the US$. The date of the rate is always 1st of the month.
I have a Tracking table that holds sales, with revenue, a sales currency, and a date. It is this revenue that I need to convert to the reporting currency.
The essential bit of code to calculate the converted value is
IIF(ISNULL(Round(Sum(trk.UpsellRevenue * xr.USDRate/ (SELECT xr1.USDRate FROM ExchangeRates AS xr1 WHERE xr1.Currency = param.report.ccy AND xr1.DateOfRate=DateSerial(Year(trk.DepartureDate),Month(trk.DepartureDate),1))),2)),
"",
Round(Sum(trk.UpsellRevenue * xr.USDRate/ (SELECT xr1.USDRate FROM ExchangeRates AS xr1 WHERE xr1.Currency = param.report.ccy AND xr1.DateOfRate=DateSerial(Year(trk.DepartureDate),Month(trk.DepartureDate),1))),2)) AS CrossValue
In essence, this works fine, but the problem is that I have to include trk.DepartureDate in the GROUP BY clause else Access complains. I don’t want to aggregate over each departure date, I want to sum over the property. So I end up with more rows than I want.
Anyone have any idea how I can achieve my goal, I just can’t see a resolution I am afraid.
Posted by: "Bob Phillips" <bob.phillips@dsl.pipex.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (1) |
Tidak ada komentar:
Posting Komentar