Bob-
Please post the entire SQL of your query.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Mar 6, 2015, at 3:25 PM, 'Bob Phillips' bob.phillips@dsl.pipex.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
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: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar