Bob-
FROM ExchangeRates AS xr1
WHERE xr1.Currency = trk.CurrencyCode AND
xr1.DateOfRate = DATESERIAL(YEAR(DepartureDate) ,MONTH(DepartureDate),1))
/(SELECT xr1.USDRate
FROM ExchangeRates AS xr1
WHERE xr1.Currency = "GBP" AND
xr1.DateOfRate = DATESERIAL(YEAR(DepartureDate),MONTH(DepartureDate),1))),2)),
"",
Round(Sum(UpsellRevenue
*(SELECT xr1.USDRate
FROM ExchangeRates AS xr1
WHERE xr1.Currency = trk.CurrencyCode AND
xr1.DateOfRate = DATESERIAL(YEAR(DepartureDate) ,MONTH(DepartureDate) ,1))
/(SELECT xr1.USDRate
FROM ExchangeRates AS xr1
WHERE xr1.Currency = "GBP" AND
xr1.DateOfRate = DATESERIAL(YEAR(DepartureDate) ,MONTH(DepartureDate),1))),2)) AS CrossValue
FROM Property AS prop
INNER JOIN (ExchangeRates AS xr
INNER JOIN Tracking AS trk ON xr.Currency = trk.CurrencyCode)
ON prop.PropertyID = trk.PropertyID
WHERE (((trk.TrackingYear)=2014) AND ((prop.PropertyID)=43) AND
((trk.DepartureDate)>=#1/1/2014# And (trk.DepartureDate)<=#12/31/2014#) AND
((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)))
Yes precisely, it complains.
I can't see how the other suggestion would work. My example was just on one property, my real case is every property. This seems more like a function call, which I don't believe Access supports.
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: 16 July 2014 16:38
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Another TRANSFORM Problem
Bob-
So if you remove the date expression from the GROUP BY it complains?
If that's the case, I suggest you do the calculation in a separate query - perhaps even without the SUM. Then SUM the result in your final Crosstab 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 Jul 16, 2014, at 11:21 AM, 'Bob Phillips' bob.phillips@dsl.pipex.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
The exchange rates table only holds rates for the 1st day of the month, so I have to determine the transaction date as the first day of the actual DepartureDate month. Thus, in my sub-select, I calculate that first of the month date to get the exchange rate. As I have an aggregate function, and as DepartureDate is in my sub-select, I have to include it in the Group By. But if I just include departure date, I get 30 rows or so, one per date not just one per month, and it is still split into groups by month.
Including DateSerial(….) has reduced the number of rows, but still too many.
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: 16 July 2014 15:00
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Another TRANSFORM Problem
Bob-
Well, you have a DateSerial in the GROUP BY of the first query, so that's why you're getting one row per month. Can you remove that?
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 Jul 16, 2014, at 9:16 AM, 'Bob Phillips' bob.phillips@dsl.pipex.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
I have several queries calculating values over a number of currencies. I originally tried cross-rate calculations by getting the rate of the transaction currency and the rate of the reporting currency and going from there. This was giving me wrong answers and I was up against a deadline, so I abandoned that and I fudged it by storing cross-rates for each reporting currency (EUR, USD, and GBP) in the rates table and using that in my query. Inevitably, that fudge has come home to roost, the users want more cross-rates (apparently China is getting important, who knew!), so I tried to my cross-rate calculation again. I think I have got it working okay now, but I now have another issue. I am pivoting across the month of the transaction, and instead of getting one line for each property, with the Jan, Feb, Mar, … values in that single row, I now get a separate row for each month, with the Jan figures in the Jan column on one row, the Feb figures in the Feb column on the next row, and so on.
Here is my revised query that lays it out incorrectly
TRANSFORM IIF(ISNULL(Round(Sum(UpsellRevenue
*(SELECT xr1.USDRate
FROM ExchangeRates AS xr1
WHERE xr1.Currency = trk.CurrencyCode AND
xr1.DateOfRate = DATESERIAL(YEAR(DepartureDate) ,MONTH(DepartureDate),1))
/(SELECT xr1.USDRate
FROM ExchangeRates AS xr1
WHERE xr1.Currency = "GBP" AND
xr1.DateOfRate = DATESERIAL(YEAR(DepartureDate),MONTH(DepartureDate),1))),2)),
"",
Round(Sum(UpsellRevenue
*(SELECT xr1.USDRate
FROM ExchangeRates AS xr1
WHERE xr1.Currency = trk.CurrencyCode AND
xr1.DateOfRate = DATESERIAL(YEAR(DepartureDate) ,MONTH(DepartureDate) ,1))
/(SELECT xr1.USDRate
FROM ExchangeRates AS xr1
WHERE xr1.Currency = "GBP" AND
xr1.DateOfRate = DATESERIAL(YEAR(DepartureDate) ,MONTH(DepartureDate),1))),2)) AS CrossValue
SELECT prop.PropertyID, prop.PropertyName
FROM Property AS prop
INNER JOIN (ExchangeRates AS xr
INNER JOIN Tracking AS trk ON xr.Currency = trk.CurrencyCode)
ON prop.PropertyID = trk.PropertyID
WHERE (((trk.TrackingYear)=2014) AND ((prop.PropertyID)=43) AND
((trk.DepartureDate)>=#1/1/2014# And (trk.DepartureDate)<=#12/31/2014#) AND
((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)))
GROUP BY prop.PropertyID, prop.PropertyName, trk.CurrencyCode, DateSerial(Year(DepartureDate),Month(DepartureDate),1)
PIVOT Format([trk.DepartureDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
And this is my current query that lays it out correctly but is restricting my reporting currencies
TRANSFORM IIf(IsNull(Round(Sum(UpsellRevenue*xr.GBPRate),2)),"",Round(Sum(UpsellRevenue*xr.GBPRate),2)) AS CrossValue
SELECT prop.PropertyID, prop.PropertyName
FROM Property AS prop
INNER JOIN (ExchangeRates AS xr
INNER JOIN Tracking AS trk ON xr.Currency = trk.CurrencyCode)
ON prop.PropertyID = trk.PropertyID
WHERE (((trk.TrackingYear)=2014) AND ((prop.PropertyID)=43) AND
((trk.DepartureDate)>=#1/1/2014# And (trk.DepartureDate)<=#12/31/2014#) AND
((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)))
GROUP BY prop.PropertyID, prop.PropertyName
PIVOT Format([trk.DepartureDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
Both queries have been reduced to the minimum to just show the problem, I have replaced all the parameters with hard-coded values, there is a SWITCH statement and more currencies tested in the second, and a lot more fields selected and joins in both. I guess the introduction of the SELECT might be causing the problem, but I am not sure how to fix it. Anyone have any ideas?
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 (6) |
Tidak ada komentar:
Posting Komentar