Selasa, 16 Agustus 2016

Re: [MS_AccessPros] QUERY TOO COMPLEX

 

Thanks. What about the first two places here where DMSkips appear, will these need to be changed too ?

Thanks.

Ade



From: "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, 16 August 2016, 18:13
Subject: Re: [MS_AccessPros] QUERY TOO COMPLEX

 
Ade-

Same advice as before - shorten all the alias names.  For example, instead of using AS DMSkips_1, use something like DMS1 or S1.

John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
(Paris, France)




On Aug 16, 2016, at 9:59 AM, Adeboyejo Oyenuga aoye_99@yahoo.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



Hi John,

i have an issue similar to the previous one. The expression DMSkips is what repeats and I am trying to shorten this. Any hint regarding this one expressed below ?

FROM ((((((DM LEFT JOIN DMSkips ON DM.Win1 = DMSkips.Number) LEFT JOIN DMSkips AS DMSkips_1 ON DM.Win2 = DMSkips_1.Number) LEFT JOIN DMSkips AS DMSkips_2 ON DM.Win3 = DMSkips_2.Number) LEFT JOIN DMSkips AS DMSkips_3 ON DM.Win4 = DMSkips_3.Number) LEFT JOIN DMSkips AS DMSkips_4 ON DM.Win5 = DMSkips_4.Number) LEFT JOIN DMSkips AS DMSkips_5 ON DM.Win6 = DMSkips_5.Number) LEFT JOIN GameSumCountRank ON DM.Sum = GameSumCountRank.Sum

Thanks.

Ade.





From: "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: MS_Access_Professionals@yahoogroups.com 
Sent: Thursday, 31 March 2016, 12:44
Subject: Re: [MS_AccessPros] QUERY TOO COMPLEX

First thing to try is to give RptMultcounts a short alias - maybe RM.  That will shorten your SQL statement significantly.

SELECT RM.IDNo, RM.Win1, … etc.

FROM (((((RptMultCounts AS RM LEFT JOIN Skppost ON RM.Win1 = Skppost.[No]) LEFT JOIN …

Copy off to Notepad and do a Replace of all occurrences of RptMultCounts (except the first one in the FROM clause) with RM, then add the alias.

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 1:20 PM, Adeboyejo Oyenuga aoye_99@yahoo.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



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];

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

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.

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: Adeboyejo Oyenuga <aoye_99@yahoo.co.uk>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (14)

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? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.


.

__,_._,___

Tidak ada komentar:

Poskan Komentar