Bob-
I can't see any reason why it's insisting you include trk.DepartureDate in the GROUP BY. You could try fooling it by replacing the subquery in your expression:
(SELECT xr1.USDRate FROM ExchangeRates AS xr1 WHERE xr1.Currency = param.report.ccy AND xr1.DateOfRate=DateSerial(Year(trk.DepartureDate),Month(trk.DepartureDate),1))
with:
DLookup("USDRate", "ExchangeRates", "Currency = '" & param.report.ccy "' AND DateOfRate = #" & DateSerial(Year(trk.DepartureDate), Month(trk.DepartureDate), 1) & "#")
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:54 PM, 'Bob Phillips' bob.phillips@dsl.pipex.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Okay
PARAMETERS param.[year] Long, param.report.ccy Text ( 3 ), param.property.id Long, param.country.id Long, param.city.id Long, param.division.id Long, param.region.id Long, param.area.id Long, param.brand.id Long, param.contract.id Long, param.start.[date] DateTime, param.[end].[date] DateTime;
TRANSFORM 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
SELECT prop.PropertyID, prop.PropertyName, '', cty.Country, div.Division, reg.Region, area.Area, brnd.Brand, con.Contract
FROM ((((((((ExchangeRates AS xr INNER JOIN Tracking AS trk ON xr.Currency = trk.CurrencyCode) INNER JOIN Property AS prop ON trk.PropertyID = prop.PropertyID) INNER JOIN Area ON prop.AreaID = area.AreaID) INNER JOIN Brand AS brnd ON prop.BrandID = brnd.BrandID) INNER JOIN City ON prop.CityID = city.CityID) INNER JOIN Country AS cty ON prop.CountryID = cty.CountryID) INNER JOIN Division AS div ON prop.DivisionID = div.DivisionID) INNER JOIN Contract AS con ON prop.ContractID = con.ContractID) INNER JOIN Region AS reg ON prop.RegionID = reg.RegionID
WHERE trk.TrackingYear=param.year And
(prop.PropertyId=param.property.id OR param.property.id=0) And
(cty.CountryID=param.country.id Or param.country.id=0) And
(reg.RegionID=param.region.id Or param.region.id=0) And
(div.DivisionID=param.division.id Or param.division.id=0) And
(area.AreaID=param.area.id Or param.area.id=0) And
(brnd.BrandID=param.brand.id Or param.brand.id=0) And
(con.ContractID=param.contract.id Or param.contract.id=0)
AND (trk.DepartureDate>=param.start.date AND trk.DepartureDate<=param.end.date) AND
xr.DateOfRate=DateSerial(Year(trk.DepartureDate),Month(trk.DepartureDate),1)
GROUP BY prop.PropertyID, prop.PropertyName, cty.Country, city.City, reg.Region, div.Division, area.Area, brnd.Brand, con.Contract
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: 06 March 2015 14:52
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Currency Code Conversion - Too Many Rows
Sent: 06 March 2015 14:52
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Currency Code Conversion - Too Many Rows
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 (4) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar