I have always considered using an alias in another expression as walking on thin ice. I don't care if it worked before, I just wouldn't do it.
Did you try change your query as suggested?
On Thu, Mar 31, 2016 at 12:51 PM -0700, "Adeboyejo Oyenuga aoye_99@yahoo.co.uk [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Actually the issue is not with the alias as in Mean. Rather I think there are some coding in later version of Access that make them generate the error message QUERY TOO COMPLEX. Afterall, the same queries ran seamlessly in earlier versions like Access 2007.
Ade
From: "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>
Sent: Thursday, 31 March 2016, 17:17
Subject: RE: [MS_AccessPros] QUERY TOO COMPLEX
Ade,
I expect your issue is creating an expression and giving it an alias "AS Mean" and then using the alias in following expressions. I never do this. Try replace the aliases with the original expression. For instance, replace every instance of Mean with ([Win1]+[Win2]+[Win3]+[Win4]+[Win5]+[Win6])/6. Do this will all alias usage. If you have to do this enough times, it might be good motivation to normalize the tables or maybe create some user-defined functions.
Duane Hookom, MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Thu, 31 Mar 2016 11:20:45 +0000
Subject: Re: [MS_AccessPros] QUERY TOO COMPLEX
From: "John Viescas JohnV@msn.com [MS_Access_Professionals]" ccess 2007.
From: "Adeboyejo Oyenuga aoye_99@yahoo.co.uk [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: yahoogroups <ms_access_professionals@yahoogroups.com>
Sent: Wednesday, 30 March 2016, 20:55
Subject: [MS_AccessPros] QUERY TOO COMPLEX
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Thu, 31 Mar 2016 11:20:45 +0000
Subject: Re: [MS_AccessPros] QUERY TOO COMPLEX
Please see the
SQL for one of them.
SELECT RptMultcounts.IDNo, RptMultcounts.Win1, RptMultcounts.Win2, RptMultcounts.Win3, RptMultcounts.Win4, RptMultcounts.Win5, RptMultcounts.Win6, RptMultcounts.Sum, RptMultcounts.RptMultSum, RptMultcounts.Summ, RptMultcounts.[10GSum], RptMultcounts.[10gmhitcount2], ([Win1]+[Win2]+[Win3]+[Win4]+[Win5]+[Win6])/6 AS Mean, ([Win1]-[Mean])*([Win1]-[Mean]) AS Meandev1, ([Win2]-[Mean])*([Win2]-[Mean]) AS Meandev2, ([Win3]-[Mean])*([Win3]-[Mean]) AS Meandev3, ([Win4]-[Mean])*([Win4]-[Mean]) AS Meandev4, ([Win5]-[Mean])*([Win5]-[Mean]) AS Meandev5, ([Win6]-[Mean])*([Win6]-[Mean]) AS Meandev6, ([Meandev1]+[Meandev2]+[Meandev3]+[Meandev4]+[Meandev5]+[Meandev6])/5 AS Meandevavg, Sqr([Meandevavg]) AS Std, Round([Std],0) AS Stddvt, Round([Std]/[Mean],0) AS Coeffvari, ([Win4]+[Win3])/2 AS Median, 3*([Mean]-[Median])/[Std] AS Skew, Round([Skew],0) AS Skewness, IIf([Skewness]=0,0,[Skewness]) AS Skew2, RptMultcounts.LngSkpcount, RptMultcounts.LngSkpcount2, RptMultcounts.Spread, RptMultcounts.HighGap, RptMultcounts.Astrocount2, Val([High] & [Low] & [Other]) AS Hitinskpcount, [High]+[Low] AS HiLow, IIf([Skppost.Skppost]='H',1,0)+IIf([Skppost_1.Skppost]='H',1,0)+IIf([Skppost_2.Skppost]='H',1,0)+IIf([Skppost_3.Skppost]='H',1,0)+IIf([Skppost_4.Skppost]='H',1,0)+IIf([Skppost_5.Skppost]='H',1,0) AS High, IIf([Skppost.Skppost]='L',1,0)+IIf([Skppost_1.Skppost]='L',1,0)+IIf([Skppost_2.Skppost]='L',1,0)+IIf([Skppost_3.Skppost]='L',1,0)+IIf([Skppost_4.Skppost]='L',1,0)+IIf([Skppost_5.Skppost]='L',1,0) AS Low, IIf([Skppost.Skppost]='O',1,0)+IIf([Skppost_1.Skppost]='O',1,0)+IIf([Skppost_2.Skppost]='O',1,0)+IIf([Skppost_3.Skppost]='O',1,0)+IIf([Skppost_4.Skppost]='O',1,0)+IIf([Skppost_5.Skppost]='O',1,0) AS Other, Val([Best1] & [Best2] & [Bestoth]) AS Bestchrt6count, IIf([Skppost.Bestchrt6]='B1',1,0)+IIf([Skppost_1.Bestchrt6]='B1',1,0)+IIf([Skppost_2.Bestchrt6]='B1',1,0)+IIf([Skppost_3.Bestchrt6]='B1',1,0)+IIf([Skppost_4.Bestchrt6]='B1',1,0)+IIf([Skppost_5.Bestchrt6]='B1',1,0) AS Best1, IIf([Skppost.Bestchrt6]='B2',1,0)+IIf([Skppost_1.Bestchrt6]='B2',1,0)+IIf([Skppost_2.Bestchrt6]='B2',1,0)+IIf([Skppost_3.Bestchrt6]='B2',1,0)+IIf([Skppost_4.Bestchrt6]='B2',1,0)+IIf([Skppost_5.Bestchrt6]='B2',1,0) AS Best2, IIf([Skppost.Bestchrt6]='O',1,0)+IIf([Skppost_1.Bestchrt6]='O',1,0)+IIf([Skppost_2.Bestchrt6]='O',1,0)+IIf([Skppost_3.Bestchrt6]='O',1,0)+IIf([Skppost_4.Bestchrt6]='O',1,0)+IIf([Skppost_5.Bestchrt6]='O',1,0) AS Bestoth, Val([Trail7] & [Trailother]) AS TrailNocount, IIf([Skppost.TrailNo]='T7',1,0)+IIf([Skppost_1.TrailNo]='T7',1,0)+IIf([Skppost_2.TrailNo]='T7',1,0)+IIf([Skppost_3.TrailNo]='T7',1,0)+IIf([Skppost_4.TrailNo]='T7',1,0)+IIf([Skppost_5.TrailNo]='T7',1,0) AS Trail7, IIf([Skppost.TrailNo]='O',1,0)+IIf([Skppost_1.TrailNo]='O',1,0)+IIf([Skppost_2.TrailNo]='O',1,0)+IIf([Skppost_3.TrailNo]='O',1,0)+IIf([Skppost_4.TrailNo]='O',1,0)+IIf([Skppost_5.TrailNo]='O',1,0) AS Trailother
FROM (((((RptMultcounts LEFT JOIN Skppost ON RptMultcounts.Win1 = Skppost.[No]) LEFT JOIN Skppost AS Skppost_1 ON RptMultcounts.Win2 = Skppost_1.[No]) LEFT JOIN Skppost AS Skppost_2 ON RptMultcounts.Win3 = Skppost_2.[No]) LEFT JOIN Skppost AS Skppost_3 ON RptMultcounts.Win4 = Skppost_3.[No]) LEFT JOIN Skppost AS Skppost_4 ON RptMultcounts.Win5 = Skppost_4.[No]) LEFT JOIN Skppost AS Skppost_5 ON RptMultcounts.Win6 = Skppost_5.[No];
FROM (((((RptMultcounts LEFT JOIN Skppost ON RptMultcounts.Win1 = Skppost.[No]) LEFT JOIN Skppost AS Skppost_1 ON RptMultcounts.Win2 = Skppost_1.[No]) LEFT JOIN Skppost AS Skppost_2 ON RptMultcounts.Win3 = Skppost_2.[No]) LEFT JOIN Skppost AS Skppost_3 ON RptMultcounts.Win4 = Skppost_3.[No]) LEFT JOIN Skppost AS Skppost_4 ON RptMultcounts.Win5 = Skppost_4.[No]) LEFT JOIN Skppost AS Skppost_5 ON RptMultcounts.Win6 = Skppost_5.[No];
This worked perfectly in Access 2007.
Thanks.
Ade
From: "John Viescas JohnV@msn.com [MS_Access_Professionals]" ccess 2007.
Thanks.
Ade<MS_Access_Professionals@yahoogroups.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Thursday, 31 March 2016, 11:59
Subject: Re: [MS_AccessPros] QUERY TOO COMPLEX
To: MS_Access_Professionals@yahoogroups.com
Sent: Thursday, 31 March 2016, 11:59
Subject: Re: [MS_AccessPros] QUERY TOO COMPLEX
Ade-
Please post the SQL from one of the queries that's failing.
The query engine / optimizer does change some between releases. I has to be able to "compile" your SQL in its buffer before it can execute it. It may have been close to the limit in 2007, but now won't fit in 2010, but there's no indication other than the Too Complex message.
There are things you can do to try to reduce the compile size - such as substitute short alias names.
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 31, 2016, at 2:24 AM, Adeboyejo Oyenuga aoye_99@yahoo.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Kindly note that these queries worked perfectly in
Access 2007 on the older machine but threw up this response in Access 2010.
Access 2007 on the older machine but threw up this response in Access 2010.
Thanks.
Ade
From: "Adeboyejo Oyenuga aoye_99@yahoo.co.uk [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: yahoogroups <ms_access_professionals@yahoogroups.com>
Sent: Wednesday, 30 March 2016, 20:55
Subject: [MS_AccessPros] QUERY TOO COMPLEX
Kindly assist. I have some queries which worked perfectly on an older computer. I get the message QUERY TOO COMPLEX when I tried running some of them on a newer computer.
Would you know how to resolve this ?
Thanks.
Ade.
__._,_.___
Posted by: Duane Hookom <duanehookom@hotmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (8) |
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? The Yahoo Mail app is fast, beautiful and intuitive. Try it today!
.
__,_._,___
