ACCESS FEED: RE: [MS_AccessPros] Another TRANSFORM Problem

John,

Okay, so I added PropertyID to the XRate query, and put XRate in the FROM clause, and it does run without that prompt.

I have two fundamental problems though.

The first is that what was a very rapid query now takes an unacceptably long time.

The second is that the results are way off. The revenue is far too large, and I am getting results for months that have no data.

Looking at the way the queries are built, it seems to me that the XRate query is calculating the revenue for the whole table, well maybe all items with the parameter year. What I really need is for it to just process the relevant property in turn, but SQL isn’t processing one at a time, so I cannot pass the single property id to the XRate query. If it is doing that, it explains why it is so slow, and why the numbers are too large (although I would have expected every property to get the same results, and they don’t).

Any thoughts on this?

Bob

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: 16 July 2014 23:54
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Another TRANSFORM Problem

John,

I think that was the bit I got, and I built a slightly different query called XRate based upon your suggestion (i.e. just doing the cross-rate calculation, not the sum), but my calling query prompted me for XRate.CrossValue as though that were a parameter. That is what made me say it seems more like a function, certainly the way I am trying to use it; I can’t be calling the XRate query correctly.

PARAMETERS param.[year] Long, param.report.ccy Text ( 3 ), param.property.id Long, param.start.[date] DateTime, param.[end].[date] DateTime;
SELECT ROUND(trk.UpsellRevenue
        *(SELECT xr1.USDRate
          FROM ExchangeRates AS xr1
          WHERE xr1.Currency = trk.CurrencyCode AND
                xr1.DateOfRate = DATESERIAL(YEAR(trk.DepartureDate) ,MONTH(trk.DepartureDate),1))
        /(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
FROM Tracking AS trk INNER JOIN ExchangeRates AS xr ON xr.Currency = trk.CurrencyCode
WHERE trk.TrackingYear=param.year AND
              (trk.PropertyID=param.property.id OR param.property.id = 99) AND
              (trk.DepartureDate>=param.start.date AND trk.DepartureDate<=param.end.date) AND
              xr.DateOfRate=DateSerial(Year(trk.DepartureDate),Month(trk.DepartureDate),1);

PARAMETERS param.[year] Long, param.report.ccy Text ( 255 ), param.property.id Long, param.division.id Long, param.start.[date] DateTime, param.[end].[date] DateTime;
TRANSFORM IIf(IsNull(Sum(XRate.CrossValue)),"",Sum(XRate.CrossValue)) AS XValue
SELECT prop.PropertyID, prop.PropertyName, div.Division
FROM (Property AS prop INNER JOIN Division AS div ON prop.DivisionID = div.DivisionID) INNER JOIN Tracking AS trk ON prop.PropertyID = trk.PropertyID
WHERE (prop.PropertyId=param.property.id OR prop.PropertyId=param.property.id) AND
(div.DivisionID=param.division.id OR div.DivisionID=param.division.id)
GROUP BY prop.PropertyID, prop.PropertyName, div.Division
PIVOT Format(trk.DepartureDate,"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: 16 July 2014 20:27
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Another TRANSFORM Problem

Bob-

What I meant is:

SELECT prop.PropertyID, prop.PropertyName, 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
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)))

Put back your parameters as necessary (and be sure to explicitly declare them with a PARAMETERS clause), save it as a separate query, then build your Crosstab on the above 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

http://www.viescas.com/

(Paris, France)

On Jul 16, 2014, at 3:06 PM, 'Bob Phillips' bob.phillips@dsl.pipex.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

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

http://www.viescas.com/

(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 1^st 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

http://www.viescas.com/

(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: "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 (9)