Bob-
Hello John,
Don't say things like that, if that happens I am really up the creek.
Param are parameters that I pass to the query (I usually declare these at the head of the query). I am not using the Access client, I am driving this from Excel using ADO. The 99 stuff is because I have a series of dropdownjs in the Excel sheet that list say the regions, but I pre-pend that list with an [All] option, that is what the 99 is referring to.
3 or 4 parameters? It restrictive isn't it?
Bob
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: 29 January 2014 20:02
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] RE: SQL Code Corruption
Bob-
It's not certain that even designing in SQL view that the query engine won't redo your code. BTW, what is "param"? A form that's open? If you're trying to drive this from a form, you should build the SQL on the fly in the parameter form and avoid all the ="99" stuff.
It's common to recommend building a query driven off a parameter form as:
WHERE ((MyField = [Forms]![Parameters]![txtFieldParm]) Or ([Forms]![Parameters]![txtFieldParm] IS NULL))
.. so that you get all rows if the filter parameter isn't supplied. But that advice is also given with a caveat that you can't do this for more than 3 or maybe 4 parameters before you'll hit the "query too complex" barrier. At that point, we recommend building the query (or filter to open a form or report) on the fly, ignoring the "null" parameters.
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 Jan 29, 2014, at 7:11 PM, Bob Phillips <bob.phillips@dsl.pipex.com> wrote:
No offence John, but that is ridiculous. I can't change the relationship view as it corrupts my code, I can't add another table via the designer because it corrupts the code, and so on. I have to do everything in the SQL view, which I find a challenge as Access seems to object to the way I join tables and insists on a oblique nesting and proliferation of brackets.
I hadn't tried to run this 'optimized' code before, but I just tried it and yes, I got that message.
I am so glad that SQL Server is my primary tool, it has some quirks but nothing as out there as that.
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: 29 January 2014 16:03
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] RE: SQL Code Corruption
Bob-
That's "normal". Access has "optimized" your predicate and switched it from a series of (A Or B) And (C or D) ["And"ed "Or"s] to this:
(A And C) Or (A And C) Or (B And C) Or (B And D) ["Or"ed "And"s)
In theory, it does that because it can quit looking when the first set of (X And Y) come up true - it doesn't have to evaluate the entire predicate for each row. As originally stated, it has to evaluate ALL the predicates to make a decision.
Did you get "query too complex" when you tried to run it?
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 Jan 29, 2014, at 4:34 PM, <wrmosca@comcast.net> <wrmosca@comcast.net> wrote:
Bob
For the life of me, I've never seen that happen. Just for kicks I did a count on [param].[property].[id] and found 64 instances! And I see everything got bracketed.
Maybe if you declare all your parameters that won't happen. But I really don't have an explanation for you.
Regards,
Bill Mosca, Founder - MS_Access_Professionals
Microsoft Office Access MVP
http://mvp.microsoft.com/en-us/mvp/Bill%20Mosca-35852
My nothing-to-do-with-Access blog
---In MS_Access_Professionals@yahoogroups.com, <bob.phillips@dsl.pipex.com> wrote:This is a bit of an odd one, but here goes.
I have this Access query.
SELECT reg.RegionID, reg.Region, IIF(ISNULL(Round(Sum(UpsellRevenue
*Switch(param.report.ccy="EUR",xr.EURRate
,param.report.ccy="USD",xr.USDRate
,param.report.ccy="GBP",xr.GBPRate)
)
,2)),""
,Round(Sum(UpsellRevenue
*Switch(param.report.ccy="EUR",xr.EURRate
,param.report.ccy="USD",xr.USDRate
,param.report.ccy="GBP",xr.GBPRate)
)
,2)) AS TotalRevenue
FROM ExchangeRates AS xr, (((Region AS reg INNER JOIN ((Country AS cty INNER JOIN Property AS prop ON cty.CountryID = prop.CountryID) INNER JOIN Tracking AS trk ON prop.PropertyID = trk.PropertyID) ON reg.RegionID = cty.RegionID) INNER JOIN Area AS area ON prop.AreaID = area.AreaID) INNER JOIN Brand AS brnd ON prop.BrandID = brnd.BrandID) INNER JOIN Contract AS con ON prop.ContractID = con.ContractID
WHERE (((trk.TrackingYear)=[param].[year])) AND
(trk.DepartureDate >= param.start.date AND trk.DepartureDate <= param.end.date) AND
(prop.PropertyID=param.property.id OR param.property.id=99) AND
(cty.CountryID=param.country.code OR param.country.code="zzz") AND
(reg.RegionID=param.region.id OR param.region.id="zzz") AND
(area.AreaID=param.area.id OR param.area.id=99) AND
(brnd.BrandID=param.brand.id OR param.brand.id=99) AND
(con.ContractID=param.contract.id OR param.contract.id=99) AND
xr.DateOfRate=DateSerial(Year(trk.DepartureDate),Month(trk.DepartureDate),1)
GROUP BY reg.RegionID, reg.Region;
If I go to design view, I find that one of the tables is hidden behind another, so I move the top one, nothing more than just moving it in the relationship poane, go back to the SQL view, and the code is changed to the below. What the heck is going on here?
SELECT reg.RegionID, reg.Region, IIf(IsNull(Round(Sum(UpsellRevenue*Switch(param.report.ccy="EUR",xr.EURRate,param.report.ccy="USD",xr.USDRate,param.report.ccy="GBP",xr.GBPRate)),2)),"",Round(Sum(UpsellRevenue*Switch(param.report.ccy="EUR",xr.EURRate,param.report.ccy="USD",xr.USDRate,param.report.ccy="GBP",xr.GBPRate)),2)) AS TotalRevenue
FROM ExchangeRates AS xr, Region AS reg INNER JOIN ((Country AS cty INNER JOIN (Brand AS brnd INNER JOIN (Area INNER JOIN (Property AS prop INNER JOIN Contract AS con ON prop.ContractID = con.ContractID) ON area.AreaID = prop.AreaID) ON brnd.BrandID = prop.BrandID) ON cty.CountryID = prop.CountryID) INNER JOIN Tracking AS trk ON prop.PropertyID = trk.PropertyID) ON reg.RegionID = cty.RegionID
WHERE (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1))) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz")) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz")) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[region].[id])="zzz")) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[region].[id])="zzz")) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz")) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz")) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[area].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[area].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[area].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[area].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[brand].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[brand].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[brand].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[brand].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((area.AreaID)=[param].[area].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[region].[id])="zzz") AND (([param].[brand].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((area.AreaID)=[param].[area].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[region].[id])="zzz") AND (([param].[brand].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((area.AreaID)=[param].[area].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[brand].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((area.AreaID)=[param].[area].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[brand].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[region].[id])="zzz") AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[region].[id])="zzz") AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[area].[id])=99) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[area].[id])=99) AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[area].[id])=99) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[area].[id])=99) AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99) AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99) AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((area.AreaID)=[param].[area].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[region].[id])="zzz") AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((area.AreaID)=[param].[area].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[region].[id])="zzz") AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((area.AreaID)=[param].[area].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((area.AreaID)=[param].[area].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99))
GROUP BY reg.RegionID, reg.Region;
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (8) |
Tidak ada komentar:
Posting Komentar