Ade,
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 17 Aug 2016 07:16:17 -0700
Subject: Re: [MS_AccessPros] QUERY TOO COMPLEX
On Aug 17, 2016, at 05:29, Adeboyejo Oyenuga aoye_99@yahoo.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
From: "Adeboyejo Oyenuga aoye_99@yahoo.co.uk [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, 17 August 2016, 13:02
Subject: Re: [MS_AccessPros] QUERY TOO COMPLEX
From: "Adeboyejo Oyenuga aoye_99@yahoo.co.uk [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, 17 August 2016, 10:55
Subject: Re: [MS_AccessPros] QUERY TOO COMPLEX
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, 20:08
Subject: Re: [MS_AccessPros] QUERY TOO COMPLEX
From: "wrmosca@comcast.net [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, 16 August 2016, 19:23
Subject: Re: [MS_AccessPros] QUERY TOO COMPLEX
---In MS_Access_Professionals@yahoogroups.com, <aoye_99@yahoo.co.uk> wrote :
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
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
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
I'm surprised the query works with any amount of data.
There are:
- 426 IIf()s
You can replace IIF([DMSkips_1].[Clusththt]=2,1,0)
With Abs([DMSkips_1].[Clusththt]=2)
- 21 Val()s
- 66 Between
- 194 Ands
You are also using alias columns in other expressions
VAL([1-9] & [10-19] & [20-29] & [30-39])
I find the previous expression confusing since you seem to be converting a concatenated string to a number.
I think you are trying to do way too much with a single query and the data seems a bit un-normalized.
Regards,
Duane
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 17 Aug 2016 07:16:17 -0700
Subject: Re: [MS_AccessPros] QUERY TOO COMPLEX
Ade-
The correct syntax is [S_1].[Clustskp]. You could copy and paste to Notepad and use Find / Replace, then copy and paste back.
That's a really ugly query! I have no clue why Query Too Complex would pop up when you have a lot of data but not when there is not much data. Does the "shortened" query work with a large amount of data?
John Viescas, author
John Viescas, author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Access Applications
On Aug 17, 2016, at 05:29, Adeboyejo Oyenuga aoye_99@yahoo.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Just to add that the query runs perfectly without amendment if the data is not large. Its when the data becomes large that issues arise .
Ade
From: "Adeboyejo Oyenuga aoye_99@yahoo.co.uk [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, 17 August 2016, 13:02
Subject: Re: [MS_AccessPros] QUERY TOO COMPLEX
I think the problem is that for some strange reasons the system will only allow for example [S_1]. [Clustskp] instead of [S_1.Clusthtskp].
Would you know a general remedy for this or do i have to go the laborious way of changing one by one ?
Thanks.
Ade
From: "Adeboyejo Oyenuga aoye_99@yahoo.co.uk [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, 17 August 2016, 10:55
Subject: Re: [MS_AccessPros] QUERY TOO COMPLEX
Thanks John. I did what you suggestion but the query is not running on account of invalid bracketing in S.Astrgrp.
I know the query without amendment is correct but the difficulty is that it is too complex for the computer to evaluate while the amended version will not run. Please take a look at both versions.
WITHOUT AMENDMENT
SELECT DM.IDNo, DM.Win1, DM.Win2, DM.Win3, DM.Win4, DM.Win5, DM.Win6, DM.Sum, [Win6]-[Win1]+1 AS Spread, DMSkips.Skip, DMSkips_1.Skip, DMSkips_2.Skip, DMSkips_3.Skip, DMSkips_4.Skip, DMSkips_5.Skip, DMSkips.Lngstskp, DMSkips_1.Lngstskp, DMSkips_2.Lngstskp, DMSkips_3.Lngstskp, DMSkips_4.Lngstskp, DMSkips_5.Lngstskp, DMSkips.DblHR, DMSkips_1.DblHR, DMSkips_2.DblHR, DMSkips_3.DblHR, DMSkips_4.DblHR, DMSkips_5.DblHR, DMSkips.Lstskp, DMSkips_1.Lstskp, DMSkips_2.Lstskp, DMSkips_3.Lstskp, DMSkips_4.Lstskp, DMSkips_5.Lstskp, DMSkips.Avgskp, DMSkips_1.Avgskp, DMSkips_2.Avgskp, DMSkips_3.Avgskp, DMSkips_4.Avgskp, DMSkips_5.Avgskp, DMSkips.StdACT, DMSkips_1.StdACT, DMSkips_2.StdACT, DMSkips_3.StdACT, DMSkips_4.StdACT, DMSkips_5.StdACT, DMSkips.[HR%], DMSkips_1.[HR%], DMSkips_2.[HR%], DMSkips_3.[HR%], DMSkips_4.[HR%], DMSkips_5.[HR%], IIf([Win1] Between 1 And 9,1,0)+IIf([Win2] Between 1 And 9,1,0)+IIf([Win3] Between 1 And 9,1,0)+IIf([Win4] Between 1 And 9,1,0)+IIf([Win5] Between 1 And 9,1,0)+IIf([Win6] Between 1 And 9,1,0) AS [1-9], IIf([Win1] Between 10 And 19,1,0)+IIf([Win2] Between 10 And 19,1,0)+IIf([Win3] Between 10 And 19,1,0)+IIf([Win4] Between 10 And 19,1,0)+IIf([Win5] Between 10 And 19,1,0)+IIf([Win6] Between 10 And 19,1,0) AS [10-19], IIf([Win1] Between 20 And 29,1,0)+IIf([Win2] Between 20 And 29,1,0)+IIf([Win3] Between 20 And 29,1,0)+IIf([Win4] Between 20 And 29,1,0)+IIf([Win5] Between 20 And 29,1,0)+IIf([Win6] Between 20 And 29,1,0) AS [20-29], IIf([Win1] Between 30 And 39,1,0)+IIf([Win2] Between 30 And 39,1,0)+IIf([Win3] Between 30 And 39,1,0)+IIf([Win4] Between 30 And 39,1,0)+IIf([Win5] Between 30 And 39,1,0)+IIf([Win6] Between 30 And 39,1,0) AS [30-39], Val([1-9] & [10-19] & [20-29] & [30-39]) AS Ngrpcount, Val([Astro8] & [AstroOth]) AS Astrocount, Val([Astro8] & [Astro12] & [Others]) AS Astrocount2, IIf([DMSkips.AstrGrp]='Astr8',1,0)+IIf([DMSkips_1.AstrGrp]='Astr8',1,0)+IIf([DMSkips_2.AstrGrp]='Astr8',1,0)+IIf([DMSkips_3.AstrGrp]='Astr8',1,0)+IIf([DMSkips_4.AstrGrp]='Astr8',1,0)+IIf([DMSkips_5.AstrGrp]='Astr8',1,0) AS Astro8, IIf([DMSkips.AstrGrp]='Astr12',1,0)+IIf([DMSkips_1.AstrGrp]='Astr12',1,0)+IIf([DMSkips_2.AstrGrp]='Astr12',1,0)+IIf([DMSkips_3.AstrGrp]='Astr12',1,0)+IIf([DMSkips_4.AstrGrp]='Astr12',1,0)+IIf([DMSkips_5.AstrGrp]='Astr12',1,0) AS Astro12, IIf([DMSkips.AstrGrp]='Others',1,0)+IIf([DMSkips_1.AstrGrp]='Others',1,0)+IIf([DMSkips_2.AstrGrp]='Others',1,0)+IIf([DMSkips_3.AstrGrp]='Others',1,0)+IIf([DMSkips_4.AstrGrp]='Others',1,0)+IIf([DMSkips_5.AstrGrp]='Others',1,0)+IIf([DMSkips.AstrGrp]='Astr12',1,0)+IIf([DMSkips_1.AstrGrp]='Astr12',1,0)+IIf([DMSkips_2.AstrGrp]='Astr12',1,0)+IIf([DMSkips_3.AstrGrp]='Astr12',1,0)+IIf([DMSkips_4.AstrGrp]='Astr12',1,0)+IIf([DMSkips_5.AstrGrp]='Astr12',1,0) AS AstroOth, IIf([DMSkips.AstrGrp]='Others',1,0)+IIf([DMSkips_1.AstrGrp]='Others',1,0)+IIf([DMSkips_2.AstrGrp]='Others',1,0)+IIf([DMSkips_3.AstrGrp]='Others',1,0)+IIf([DMSkips_4.AstrGrp]='Others',1,0)+IIf([DMSkips_5.AstrGrp]='Others',1,0) AS Others, Val([ClhtLowSkp] & [Clht6-9Skp] & [ClhtCOLD]) AS ClhtSkipcount, IIf([DMSkips.Clusthtskp] Between 0 And 5,1,0)+IIf([DMSkips_1.Clusthtskp] Between 0 And 5,1,0)+IIf([DMSkips_2.Clusthtskp] Between 0 And 5,1,0)+IIf([DMSkips_3.Clusthtskp] Between 0 And 5,1,0)+IIf([DMSkips_4.Clusthtskp] Between 0 And 5,1,0)+IIf([DMSkips_5.Clusthtskp] Between 0 And 5,1,0) AS ClhtLowSkp, IIf([DMSkips.Clusthtskp] Between 6 And 9,1,0)+IIf([DMSkips_1.Clusthtskp] Between 6 And 9,1,0)+IIf([DMSkips_2.Clusthtskp] Between 6 And 9,1,0)+IIf([DMSkips_3.Clusthtskp] Between 6 And 9,1,0)+IIf([DMSkips_4.Clusthtskp] Between 6 And 9,1,0)+IIf([DMSkips_5.Clusthtskp] Between 6 And 9,1,0) AS [Clht6-9Skp], IIf([DMSkips.Clusthtskp] Between 10 And 70,1,0)+IIf([DMSkips_1.Clusthtskp] Between 10 And 70,1,0)+IIf([DMSkips_2.Clusthtskp] Between 10 And 70,1,0)+IIf([DMSkips_3.Clusthtskp] Between 10 And 70,1,0)+IIf([DMSkips_4.Clusthtskp] Between 10 And 70,1,0)+IIf([DMSkips_5.Clusthtskp] Between 10 And 70,1,0) AS ClhtCOLD, Val([ClskpLowSkp] & [Clskp6-9Skp] & [ClskpCOLD]) AS ClSkpSkipcount, IIf([DMSkips.Clustskpskp] Between 0 And 5,1,0)+IIf([DMSkips_1.Clustskpskp] Between 0 And 5,1,0)+IIf([DMSkips_2.Clustskpskp] Between 0 And 5,1,0)+IIf([DMSkips_3.Clustskpskp] Between 0 And 5,1,0)+IIf([DMSkips_4.Clustskpskp] Between 0 And 5,1,0)+IIf([DMSkips_5.Clustskpskp] Between 0 And 5,1,0) AS ClskpLowSkp, IIf([DMSkips.Clustskpskp] Between 6 And 9,1,0)+IIf([DMSkips_1.Clustskpskp] Between 6 And 9,1,0)+IIf([DMSkips_2.Clustskpskp] Between 6 And 9,1,0)+IIf([DMSkips_3.Clustskpskp] Between 6 And 9,1,0)+IIf([DMSkips_4.Clustskpskp] Between 6 And 9,1,0)+IIf([DMSkips_5.Clustskpskp] Between 6 And 9,1,0) AS [Clskp6-9Skp], IIf([DMSkips.Clustskpskp] Between 10 And 70,1,0)+IIf([DMSkips_1.Clustskpskp] Between 10 And 70,1,0)+IIf([DMSkips_2.Clustskpskp] Between 10 And 70,1,0)+IIf([DMSkips_3.Clustskpskp] Between 10 And 70,1,0)+IIf([DMSkips_4.Clustskpskp] Between 10 And 70,1,0)+IIf([DMSkips_5.Clustskpskp] Between 10 And 70,1,0) AS ClskpCOLD, [Win2]-[Win1]-1 AS [W2-1], [Win3]-[Win2]-1 AS [W3-2], [Win4]-[Win3]-1 AS [W4-3], [Win5]-[Win4]-1 AS [W5-4], [Win6]-[Win5]-1 AS [W6-5], Val(Maxoflist([W2-1],[W3-2],[W4-3],[W5-4],[W6-5])) AS HighGap, IIf([DMSkips.HIGHLOW]='Low',1,0)+IIf([DMSkips_1.HIGHLOW]='Low',1,0)+IIf([DMSkips_2.HIGHLOW]='Low',1,0)+IIf([DMSkips_3.HIGHLOW]='Low',1,0)+IIf([DMSkips_4.HIGHLOW]='Low',1,0)+IIf([DMSkips_5.HIGHLOW]='Low',1,0) AS Low, IIf([DMSkips.HIGHLOW]='High',1,0)+IIf([DMSkips_1.HIGHLOW]='High',1,0)+IIf([DMSkips_2.HIGHLOW]='High',1,0)+IIf([DMSkips_3.HIGHLOW]='High',1,0)+IIf([DMSkips_4.HIGHLOW]='High',1,0)+IIf([DMSkips_5.HIGHLOW]='High',1,0) AS High, IIf([DMSkips.ODDEVEN]='Odd',1,0)+IIf([DMSkips_1.ODDEVEN]='Odd',1,0)+IIf([DMSkips_2.ODDEVEN]='Odd',1,0)+IIf([DMSkips_3.ODDEVEN]='Odd',1,0)+IIf([DMSkips_4.ODDEVEN]='Odd',1,0)+IIf([DMSkips_5.ODDEVEN]='Odd',1,0) AS Odd, IIf([DMSkips.ODDEVEN]='Even',1,0)+IIf([DMSkips_1.ODDEVEN]='Even',1,0)+IIf([DMSkips_2.ODDEVEN]='Even',1,0)+IIf([DMSkips_3.ODDEVEN]='Even',1,0)+IIf([DMSkips_4.ODDEVEN]='Even',1,0)+IIf([DMSkips_5.ODDEVEN]='Even',1,0) AS Even, Val([Low] & [High] & [Odd] & [Even]) AS LoHiOEcount, IIf([DMSkips.Median]=2,1,0)+IIf([DMSkips_1.Median]=2,1,0)+IIf([DMSkips_2.Median]=2,1,0)+IIf([DMSkips_3.Median]=2,1,0)+IIf([DMSkips_4.Median]=2,1,0)+IIf([DMSkips_5.Median]=2,1,0) AS Median2, IIf([DMSkips.Median]=3,1,0)+IIf([DMSkips_1.Median]=3,1,0)+IIf([DMSkips_2.Median]=3,1,0)+IIf([DMSkips_3.Median]=3,1,0)+IIf([DMSkips_4.Median]=3,1,0)+IIf([DMSkips_5.Median]=3,1,0) AS Median3, IIf([DMSkips.Median]=4,1,0)+IIf([DMSkips_1.Median]=4,1,0)+IIf([DMSkips_2.Median]=4,1,0)+IIf([DMSkips_3.Median]=4,1,0)+IIf([DMSkips_4.Median]=4,1,0)+IIf([DMSkips_5.Median]=4,1,0) AS Median4, Val([Median2] & [Median3] & [Median4]) AS Mdngrpcount, Val([Stdevskp4] & [Stdevskp5] & [Stdevskp6]) AS Stdevskpcount, IIf([DMSkips.StddevSkp]=4,1,0)+IIf([DMSkips_1.StddevSkp]=4,1,0)+IIf([DMSkips_2.StddevSkp]=4,1,0)+IIf([DMSkips_3.StddevSkp]=4,1,0)+IIf([DMSkips_4.StddevSkp]=4,1,0)+IIf([DMSkips_5.StddevSkp]=4,1,0) AS Stdevskp4, IIf([DMSkips.StddevSkp]=5,1,0)+IIf([DMSkips_1.StddevSkp]=5,1,0)+IIf([DMSkips_2.StddevSkp]=5,1,0)+IIf([DMSkips_3.StddevSkp]=5,1,0)+IIf([DMSkips_4.StddevSkp]=5,1,0)+IIf([DMSkips_5.StddevSkp]=5,1,0) AS Stdevskp5, IIf([DMSkips.StddevSkp]=6,1,0)+IIf([DMSkips_1.StddevSkp]=6,1,0)+IIf([DMSkips_2.StddevSkp]=6,1,0)+IIf([DMSkips_3.StddevSkp]=6,1,0)+IIf([DMSkips_4.StddevSkp]=6,1,0)+IIf([DMSkips_5.StddevSkp]=6,1,0) AS Stdevskp6, IIf([DMSkips.10GmHit]=0,1,0)+IIf([DMSkips_1.10GmHit]=0,1,0)+IIf([DMSkips_2.10GmHit]=0,1,0)+IIf([DMSkips_3.10GmHit]=0,1,0)+IIf([DMSkips_4.10GmHit]=0,1,0)+IIf([DMSkips_5.10GmHit]=0,1,0) AS Hit0, IIf([DMSkips.10GmHit]=1,1,0)+IIf([DMSkips_1.10GmHit]=1,1,0)+IIf([DMSkips_2.10GmHit]=1,1,0)+IIf([DMSkips_3.10GmHit]=1,1,0)+IIf([DMSkips_4.10GmHit]=1,1,0)+IIf([DMSkips_5.10GmHit]=1,1,0) AS Hit1, IIf([DMSkips.10GmHit]=2,1,0)+IIf([DMSkips_1.10GmHit]=2,1,0)+IIf([DMSkips_2.10GmHit]=2,1,0)+IIf([DMSkips_3.10GmHit]=2,1,0)+IIf([DMSkips_4.10GmHit]=2,1,0)+IIf([DMSkips_5.10GmHit]=2,1,0) AS Hit2, IIf([DMSkips.10GmHit]=3,1,0)+IIf([DMSkips_1.10GmHit]=3,1,0)+IIf([DMSkips_2.10GmHit]=3,1,0)+IIf([DMSkips_3.10GmHit]=3,1,0)+IIf([DMSkips_4.10GmHit]=3,1,0)+IIf([DMSkips_5.10GmHit]=3,1,0) AS Hit3, IIf([DMSkips.10GmHit]=4,1,0)+IIf([DMSkips_1.10GmHit]=4,1,0)+IIf([DMSkips_2.10GmHit]=4,1,0)+IIf([DMSkips_3.10GmHit]=4,1,0)+IIf([DMSkips_4.10GmHit]=4,1,0)+IIf([DMSkips_5.10GmHit]=4,1,0) AS Hit4, IIf([DMSkips.10GmHit] Between 3 And 6,1,0)+IIf([DMSkips_1.10GmHit] Between 3 And 6,1,0)+IIf([DMSkips_2.10GmHit] Between 3 And 6,1,0)+IIf([DMSkips_3.10GmHit] Between 3 And 6,1,0)+IIf([DMSkips_4.10GmHit] Between 3 And 6,1,0)+IIf([DMSkips_5.10GmHit] Between 3 And 6,1,0) AS Hitother, Val([Hit0] & [Hit1] & [Hit2] & [Hit3] & [Hit4]) AS 10gmhitcount, Val([Hit0] & [Hit1] & [Hit2] & [Hitother]) AS 10gmhitcount2, IIf([DMSkips.Clusththt]=2,1,0)+IIf([DMSkips_1.Clusththt]=2,1,0)+IIf([DMSkips_2.Clusththt]=2,1,0)+IIf([DMSkips_3.Clusththt]=2,1,0)+IIf([DMSkips_4.Clusththt]=2,1,0)+IIf([DMSkips_5.Clusththt]=2,1,0) AS Clusterhtht2, IIf([DMSkips.Clusththt]=3,1,0)+IIf([DMSkips_1.Clusththt]=3,1,0)+IIf([DMSkips_2.Clusththt]=3,1,0)+IIf([DMSkips_3.Clusththt]=3,1,0)+IIf([DMSkips_4.Clusththt]=3,1,0)+IIf([DMSkips_5.Clusththt]=3,1,0) AS Clusterhtht3, IIf([DMSkips.Clusththt]=4,1,0)+IIf([DMSkips_1.Clusththt]=4,1,0)+IIf([DMSkips_2.Clusththt]=4,1,0)+IIf([DMSkips_3.Clusththt]=4,1,0)+IIf([DMSkips_4.Clusththt]=4,1,0)+IIf([DMSkips_5.Clusththt]=4,1,0) AS Clusterhtht4, IIf([DMSkips.Clusththt]=5,1,0)+IIf([DMSkips_1.Clusththt]=5,1,0)+IIf([DMSkips_2.Clusththt]=5,1,0)+IIf([DMSkips_3.Clusththt]=5,1,0)+IIf([DMSkips_4.Clusththt]=5,1,0)+IIf([DMSkips_5.Clusththt]=5,1,0) AS Clusterhtht5, Val([Clusterhtht2] & [Clusterhtht3] & [Clusterhtht4] & [Clusterhtht5]) AS Clusththtcount, IIf([DMSkips.Clustskpht]=2,1,0)+IIf([DMSkips_1.Clustskpht]=2,1,0)+IIf([DMSkips_2.Clustskpht]=2,1,0)+IIf([DMSkips_3.Clustskpht]=2,1,0)+IIf([DMSkips_4.Clustskpht]=2,1,0)+IIf([DMSkips_5.Clustskpht]=2,1,0) AS Clusterskpht2, IIf([DMSkips.Clustskpht]=3,1,0)+IIf([DMSkips_1.Clustskpht]=3,1,0)+IIf([DMSkips_2.Clustskpht]=3,1,0)+IIf([DMSkips_3.Clustskpht]=3,1,0)+IIf([DMSkips_4.Clustskpht]=3,1,0)+IIf([DMSkips_5.Clustskpht]=3,1,0) AS Clusterskpht3, IIf([DMSkips.Clustskpht]=4,1,0)+IIf([DMSkips_1.Clustskpht]=4,1,0)+IIf([DMSkips_2.Clustskpht]=4,1,0)+IIf([DMSkips_3.Clustskpht]=4,1,0)+IIf([DMSkips_4.Clustskpht]=4,1,0)+IIf([DMSkips_5.Clustskpht]=4,1,0) AS Clusterskpht4, IIf([DMSkips.Clustskpht]=5,1,0)+IIf([DMSkips_1.Clustskpht]=5,1,0)+IIf([DMSkips_2.Clustskpht]=5,1,0)+IIf([DMSkips_3.Clustskpht]=5,1,0)+IIf([DMSkips_4.Clustskpht]=5,1,0)+IIf([DMSkips_5.Clustskpht]=5,1,0) AS Clusterskpht5, Val([Clusterskpht2] & [Clusterskpht3] & [Clusterskpht4] & [Clusterskpht5]) AS Clustskphtcount, IIf([DMSkips.SkipGrp]='LowSkip',1,0)+IIf([DMSkips_1.SkipGrp]='LowSkip',1,0)+IIf([DMSkips_2.SkipGrp]='LowSkip',1,0)+IIf([DMSkips_3.SkipGrp]='LowSkip',1,0)+IIf([DMSkips_4.SkipGrp]='LowSkip',1,0)+IIf([DMSkips_5.SkipGrp]='LowSkip',1,0) AS LowSkp, IIf([DMSkips.SkipGrp]='6-9',1,0)+IIf([DMSkips_1.SkipGrp]='6-9',1,0)+IIf([DMSkips_2.SkipGrp]='6-9',1,0)+IIf([DMSkips_3.SkipGrp]='6-9',1,0)+IIf([DMSkips_4.SkipGrp]='6-9',1,0)+IIf([DMSkips_5.SkipGrp]='6-9',1,0) AS [6-9Skp], IIf([DMSkips.SkipGrp]='COLD',1,0)+IIf([DMSkips_1.SkipGrp]='COLD',1,0)+IIf([DMSkips_2.SkipGrp]='COLD',1,0)+IIf([DMSkips_3.SkipGrp]='COLD',1,0)+IIf([DMSkips_4.SkipGrp]='COLD',1,0)+IIf([DMSkips_5.SkipGrp]='COLD',1,0) AS COLD, Val([LowSkp] & [6-9Skp] & [COLD]) AS Skipcount, IIf([DMSkips.OAvgGrp]='Zer0',1,0)+IIf([DMSkips_1.OAvgGrp]='Zer0',1,0)+IIf([DMSkips_2.OAvgGrp]='Zer0',1,0)+IIf([DMSkips_3.OAvgGrp]='Zer0',1,0)+IIf([DMSkips_4.OAvgGrp]='Zer0',1,0)+IIf([DMSkips_5.OAvgGrp]='Zer0',1,0) AS OutAvg0, IIf([DMSkips.OAvgGrp]='Two',1,0)+IIf([DMSkips_1.OAvgGrp]='Two',1,0)+IIf([DMSkips_2.OAvgGrp]='Two',1,0)+IIf([DMSkips_3.OAvgGrp]='Two',1,0)+IIf([DMSkips_4.OAvgGrp]='Two',1,0)+IIf([DMSkips_5.OAvgGrp]='Two',1,0) AS OutAvg2, IIf([DMSkips.OAvgGrp]='Other',1,0)+IIf([DMSkips_1.OAvgGrp]='Other',1,0)+IIf([DMSkips_2.OAvgGrp]='Other',1,0)+IIf([DMSkips_3.OAvgGrp]='Other',1,0)+IIf([DMSkips_4.OAvgGrp]='Other',1,0)+IIf([DMSkips_5.OAvgGrp]='Other',1,0) AS OutAvgother, Val([OutAvg0] & [OutAvg2] & [OutAvgother]) AS OutAvgcount, IIf([DMSkips.LskpGrp]='Lastzero',1,0)+IIf([DMSkips_1.LskpGrp]='Lastzero',1,0)+IIf([DMSkips_2.LskpGrp]='Lastzero',1,0)+IIf([DMSkips_3.LskpGrp]='Lastzero',1,0)+IIf([DMSkips_4.LskpGrp]='Lastzero',1,0)+IIf([DMSkips_5.LskpGrp]='Lastzero',1,0) AS Lskp0, IIf([DMSkips.LskpGrp]='Lastone',1,0)+IIf([DMSkips_1.LskpGrp]='Lastone',1,0)+IIf([DMSkips_2.LskpGrp]='Lastone',1,0)+IIf([DMSkips_3.LskpGrp]='Lastone',1,0)+IIf([DMSkips_4.LskpGrp]='Lastone',1,0)+IIf([DMSkips_5.LskpGrp]='Lastone',1,0) AS Lskp1, IIf([DMSkips.LskpGrp]='Other',1,0)+IIf([DMSkips_1.LskpGrp]='Other',1,0)+IIf([DMSkips_2.LskpGrp]='Other',1,0)+IIf([DMSkips_3.LskpGrp]='Other',1,0)+IIf([DMSkips_4.LskpGrp]='Other',1,0)+IIf([DMSkips_5.LskpGrp]='Other',1,0) AS Lskpother, Val([Lskp0] & [Lskp1] & [Lskpother]) AS LastSkpcount, Val([7High] & [7Low] & [OtherHit]) AS Hitratiocount, IIf([DMSkips.HitGrp]='7high',1,0)+IIf([DMSkips_1.HitGrp]='7high',1,0)+IIf([DMSkips_2.HitGrp]='7high',1,0)+IIf([DMSkips_3.HitGrp]='7high',1,0)+IIf([DMSkips_4.HitGrp]='7high',1,0)+IIf([DMSkips_5.HitGrp]='7high',1,0) AS 7High, IIf([DMSkips.HitGrp]='7low',1,0)+IIf([DMSkips_1.HitGrp]='7low',1,0)+IIf([DMSkips_2.HitGrp]='7low',1,0)+IIf([DMSkips_3.HitGrp]='7low',1,0)+IIf([DMSkips_4.HitGrp]='7low',1,0)+IIf([DMSkips_5.HitGrp]='7low',1,0) AS 7Low, IIf([DMSkips.HitGrp]='Other',1,0)+IIf([DMSkips_1.HitGrp]='Other',1,0)+IIf([DMSkips_2.HitGrp]='Other',1,0)+IIf([DMSkips_3.HitGrp]='Other',1,0)+IIf([DMSkips_4.HitGrp]='Other',1,0)+IIf([DMSkips_5.HitGrp]='Other',1,0) AS OtherHit, IIf([DMSkips.AvgSkGrp]='FourFour',1,0)+IIf([DMSkips_1.AvgSkGrp]='FourFour',1,0)+IIf([DMSkips_2.AvgSkGrp]='FourFour',1,0)+IIf([DMSkips_3.AvgSkGrp]='FourFour',1,0)+IIf([DMSkips_4.AvgSkGrp]='FourFour',1,0)+IIf([DMSkips_5.AvgSkGrp]='FourFour',1,0) AS Avgskp44, IIf([DMSkips.AvgSkGrp]='FourFive',1,0)+IIf([DMSkips_1.AvgSkGrp]='FourFive',1,0)+IIf([DMSkips_2.AvgSkGrp]='FourFive',1,0)+IIf([DMSkips_3.AvgSkGrp]='FourFive',1,0)+IIf([DMSkips_4.AvgSkGrp]='FourFive',1,0)+IIf([DMSkips_5.AvgSkGrp]='FourFive',1,0) AS Avgskp45, IIf([DMSkips.AvgSkGrp]='FourSix',1,0)+IIf([DMSkips_1.AvgSkGrp]='FourSix',1,0)+IIf([DMSkips_2.AvgSkGrp]='FourSix',1,0)+IIf([DMSkips_3.AvgSkGrp]='FourSix',1,0)+IIf([DMSkips_4.AvgSkGrp]='FourSix',1,0)+IIf([DMSkips_5.AvgSkGrp]='FourSix',1,0) AS Avgskp46, IIf([DMSkips.AvgSkGrp]='Others',1,0)+IIf([DMSkips_1.AvgSkGrp]='Others',1,0)+IIf([DMSkips_2.AvgSkGrp]='Others',1,0)+IIf([DMSkips_3.AvgSkGrp]='Others',1,0)+IIf([DMSkips_4.AvgSkGrp]='Others',1,0)+IIf([DMSkips_5.AvgSkGrp]='Others',1,0) AS Avgskpother, IIf([DMSkips.AvgSkGrp]='Others',1,0)+IIf([DMSkips_1.AvgSkGrp]='Others',1,0)+IIf([DMSkips_2.AvgSkGrp]='Others',1,0)+IIf([DMSkips_3.AvgSkGrp]='Others',1,0)+IIf([DMSkips_4.AvgSkGrp]='Others',1,0)+IIf([DMSkips_5.AvgSkGrp]='Others',1,0)+IIf([DMSkips.AvgSkGrp]='FourSix',1,0)+IIf([DMSkips_1.AvgSkGrp]='FourSix',1,0)+IIf([DMSkips_2.AvgSkGrp]='FourSix',1,0)+IIf([DMSkips_3.AvgSkGrp]='FourSix',1,0)+IIf([DMSkips_4.AvgSkGrp]='FourSix',1,0)+IIf([DMSkips_5.AvgSkGrp]='FourSix',1,0) AS Avgskpot2, Val([Avgskp44] & [Avgskp45] & [Avgskp46] & [Avgskpother]) AS AvgSkpcount, Val([Avgskp44] & [Avgskp45] & [Avgskpot2]) AS AvgSkpcount2, IIf([DMSkips.LngskGrp]='Twoone',1,0)+IIf([DMSkips_1.LngskGrp]='Twoone',1,0)+IIf([DMSkips_2.LngskGrp]='Twoone',1,0)+IIf([DMSkips_3.LngskGrp]='Twoone',1,0)+IIf([DMSkips_4.LngskGrp]='Twoone',1,0)+IIf([DMSkips_5.LngskGrp]='Twoone',1,0) AS Lngskp21, IIf([DMSkips.LngskGrp]='Twotwo',1,0)+IIf([DMSkips_1.LngskGrp]='Twotwo',1,0)+IIf([DMSkips_2.LngskGrp]='Twotwo',1,0)+IIf([DMSkips_3.LngskGrp]='Twotwo',1,0)+IIf([DMSkips_4.LngskGrp]='Twotwo',1,0)+IIf([DMSkips_5.LngskGrp]='Twotwo',1,0) AS Lngskp22, IIf([DMSkips.LngskGrp]='Twothree',1,0)+IIf([DMSkips_1.LngskGrp]='Twothree',1,0)+IIf([DMSkips_2.LngskGrp]='Twothree',1,0)+IIf([DMSkips_3.LngskGrp]='Twothree',1,0)+IIf([DMSkips_4.LngskGrp]='Twothree',1,0)+IIf([DMSkips_5.LngskGrp]='Twothree',1,0) AS Lngskp23, IIf([DMSkips.LngskGrp]='Other',1,0)+IIf([DMSkips_1.LngskGrp]='Other',1,0)+IIf([DMSkips_2.LngskGrp]='Other',1,0)+IIf([DMSkips_3.LngskGrp]='Other',1,0)+IIf([DMSkips_4.LngskGrp]='Other',1,0)+IIf([DMSkips_5.LngskGrp]='Other',1,0) AS Lngskpoth, IIf([DMSkips.LngskGrp]='Other',1,0)+IIf([DMSkips_1.LngskGrp]='Other',1,0)+IIf([DMSkips_2.LngskGrp]='Other',1,0)+IIf([DMSkips_3.LngskGrp]='Other',1,0)+IIf([DMSkips_4.LngskGrp]='Other',1,0)+IIf([DMSkips_5.LngskGrp]='Other',1,0)+IIf([DMSkips.LngskGrp]='Twothree',1,0)+IIf([DMSkips_1.LngskGrp]='Twothree',1,0)+IIf([DMSkips_2.LngskGrp]='Twothree',1,0)+IIf([DMSkips_3.LngskGrp]='Twothree',1,0)+IIf([DMSkips_4.LngskGrp]='Twothree',1,0)+IIf([DMSkips_5.LngskGrp]='Twothree',1,0) AS Lngskpot2, Val([Lngskp21] & [Lngskp22] & [Lngskp23] & [Lngskpoth]) AS LngSkpcount, Val([Lngskp21] & [Lngskp22] & [Lngskpot2]) AS LngSkpcount2, IIf([Win2]-[Win1]=1,1,0) AS Nhb1, IIf([Win3]-[Win2]=1,1,0) AS Nhb2, IIf([Win4]-[Win3]=1,1,0) AS Nhb3, IIf([Win5]-[Win4]=1,1,0) AS Nhb4, IIf([Win6]-[Win5]=1,1,0)+IIf([Win6]-[Win1]=38,1,0) AS Nhb5, IIf([Nhb1]=0 And [Nhb2]=0 And [Nhb3]=0 And [Nhb4]=0 And [Nhb5]=0,0,0) AS Noneighb, IIf([Nhb1]=1 And [Nhb2]=0 And [Nhb3]=1 And [Nhb4]=0 And [Nhb5]=1,222,0) AS 222Neighb, IIf([Nhb1]=1 And [Nhb2]=0 And [Nhb3]=1 And [Nhb4]=0 And [Nhb5]=0,22,0)+IIf([Nhb1]=0 And [Nhb2]=1 And [Nhb3]=0 And [Nhb4]=1 And [Nhb5]=0,22,0)+IIf([Nhb1]=0 And [Nhb2]=0 And [Nhb3]=1 And [Nhb4]=0 And [Nhb5]=1,22,0)+IIf([Nhb1]=1 And [Nhb2]=0 And [Nhb3]=0 And [Nhb4]=1 And [Nhb5]=0,22,0)+IIf([Nhb1]=1 And [Nhb2]=0 And [Nhb3]=0 And [Nhb4]=0 And [Nhb5]=1,22,0)+IIf([Nhb1]=0 And [Nhb2]=1 And [Nhb3]=0 And [Nhb4]=0 And [Nhb5]=1,22,0) AS 22Neighb, IIf([Nhb1]=1 And [Nhb2]=0 And [Nhb3]=0 And [Nhb4]=0 And [Nhb5]=0,2,0)+IIf([Nhb1]=0 And [Nhb2]=1 And [Nhb3]=0 And [Nhb4]=0 And [Nhb5]=0,2,0)+IIf([Nhb1]=0 And [Nhb2]=0 And [Nhb3]=1 And [Nhb4]=0 And [Nhb5]=0,2,0)+IIf([Nhb1]=0 And [Nhb2]=0 And [Nhb3]=0 And [Nhb4]=1 And [Nhb5]=0,2,0)+IIf([Nhb1]=0 And [Nhb2]=0 And [Nhb3]=0 And [Nhb4]=0 And [Nhb5]=1,2,0) AS 2Neighb, IIf([Nhb1]=1 And [Nhb2]=1 And [Nhb3]=0 And [Nhb4]=1 And [Nhb5]=0,32,0)+IIf([Nhb1]=0 And [Nhb2]=1 And [Nhb3]=1 And [Nhb4]=0 And [Nhb5]=1,32,0)+IIf([Nhb1]=1 And [Nhb2]=1 And [Nhb3]=0 And [Nhb4]=0 And [Nhb5]=1,32,0)+IIf([Nhb1]=1 And [Nhb2]=0 And [Nhb3]=1 And [Nhb4]=1 And [Nhb5]=0,32,0)+IIf([Nhb1]=1 And [Nhb2]=0 And [Nhb3]=0 And [Nhb4]=1 And [Nhb5]=1,32,0)+IIf([Nhb1]=0 And [Nhb2]=1 And [Nhb3]=0 And [Nhb4]=1 And [Nhb5]=1,32,0) AS 32Neighb, IIf([Nhb1]=1 And [Nhb2]=1 And [Nhb3]=0 And [Nhb4]=0 And [Nhb5]=0,3,0)+IIf([Nhb1]=0 And [Nhb2]=1 And [Nhb3]=1 And [Nhb4]=0 And [Nhb5]=0,3,0)+IIf([Nhb1]=0 And [Nhb2]=0 And [Nhb3]=1 And [Nhb4]=1 And [Nhb5]=0,3,0)+IIf([Nhb1]=0 And [Nhb2]=0 And [Nhb3]=0 And [Nhb4]=1 And [Nhb5]=1,3,0)+IIf([Nhb1]=0 And [Nhb2]=0 And [Nhb3]=0 And [Nhb4]=0 And [Nhb5]=2,3,0) AS 3Neighb, IIf([Nhb1]=1 And [Nhb2]=1 And [Nhb3]=1 And [Nhb4]=0 And [Nhb5]=1,42,0)+IIf([Nhb1]=1 And [Nhb2]=0 And [Nhb3]=1 And [Nhb4]=1 And [Nhb5]=1,42,0) AS [4+2Neighb], IIf([Nhb1]=1 And [Nhb2]=1 And [Nhb3]=1 And [Nhb4]=0 And [Nhb5]=0,4,0)+IIf([Nhb1]=0 And [Nhb2]=1 And [Nhb3]=1 And [Nhb4]=1 And [Nhb5]=0,4,0)+IIf([Nhb1]=0 And [Nhb2]=0 And [Nhb3]=1 And [Nhb4]=1 And [Nhb5]=1,4,0) AS 4Neighb, IIf([Nhb1]=1 And [Nhb2]=1 And [Nhb3]=1 And [Nhb4]=1 And [Nhb5]=0,5,0)+IIf([Nhb1]=0 And [Nhb2]=1 And [Nhb3]=1 And [Nhb4]=1 And [Nhb5]=1,5,0) AS 5Neighb, IIf([Nhb1]=1 And [Nhb2]=1 And [Nhb3]=1 And [Nhb4]=1 And [Nhb5]=1,6,0) AS 6Neighb, IIf([Noneighb]=5,0,0)+IIf([222Neighb]=222,222,0)+IIf([22Neighb]=22,22,0)+IIf([2Neighb]=2,2,0)+IIf([32Neighb]=32,32,0)+IIf([3Neighb]=3,3,0)+IIf([4+2Neighb]=42,42,0)+IIf([4Neighb]=4,4,0)+IIf([5Neighb]=5,5,0)+IIf([6Neighb]=6,6,0) AS NhbCount INTO GreatExperimentFinal
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
WHERE ((([Win6]-[Win1]+1)<>34));
WITH AMENDMENT
SELECT DM.IDNo, DM.Win1, DM.Win2, DM.Win3, DM.Win4, DM.Win5, DM.Win6, DM.Sum, [Win6]-[Win1]+1 AS Spread, S.Skip, S_1.Skip, S_2.Skip, S_3.Skip, S_4.Skip, S_5.Skip, S.Lngstskp, S_1.Lngstskp, S_2.Lngstskp, S_3.Lngstskp, S_4.Lngstskp, S_5.Lngstskp, S.DblHR, S_1.DblHR, S_2.DblHR, S_3.DblHR, S_4.DblHR, S_5.DblHR, S.Lstskp, S_1.Lstskp, S_2.Lstskp, S_3.Lstskp, S_4.Lstskp, S_5.Lstskp, S.Avgskp, S_1.Avgskp, S_2.Avgskp, S_3.Avgskp, S_4.Avgskp, S_5.Avgskp, S.StdACT, S_1.StdACT, S_2.StdACT, S_3.StdACT, S_4.StdACT, S_5.StdACT, S.[HR%], S_1.[HR%], S_2.[HR%], S_3.[HR%], S_4.[HR%], S_5.[HR%], IIf([Win1] Between 1 And 9,1,0)+IIf([Win2] Between 1 And 9,1,0)+IIf([Win3] Between 1 And 9,1,0)+IIf([Win4] Between 1 And 9,1,0)+IIf([Win5] Between 1 And 9,1,0)+IIf([Win6] Between 1 And 9,1,0) AS [1-9], IIf([Win1] Between 10 And 19,1,0)+IIf([Win2] Between 10 And 19,1,0)+IIf([Win3] Between 10 And 19,1,0)+IIf([Win4] Between 10 And 19,1,0)+IIf([Win5] Between 10 And 19,1,0)+IIf([Win6] Between 10 And 19,1,0) AS [10-19], IIf([Win1] Between 20 And 29,1,0)+IIf([Win2] Between 20 And 29,1,0)+IIf([Win3] Between 20 And 29,1,0)+IIf([Win4] Between 20 And 29,1,0)+IIf([Win5] Between 20 And 29,1,0)+IIf([Win6] Between 20 And 29,1,0) AS [20-29], IIf([Win1] Between 30 And 39,1,0)+IIf([Win2] Between 30 And 39,1,0)+IIf([Win3] Between 30 And 39,1,0)+IIf([Win4] Between 30 And 39,1,0)+IIf([Win5] Between 30 And 39,1,0)+IIf([Win6] Between 30 And 39,1,0) AS [30-39], Val([1-9] & [10-19] & [20-29] & [30-39]) AS Ngrpcount, Val([Astro8] & [AstroOth]) AS Astrocount, Val([Astro8] & [Astro12] & [Others]) AS Astrocount2, IIf([S.AstrGrp]='Astr8',1,0)+IIf([S_1.AstrGrp]='Astr8',1,0)+IIf([S_2.AstrGrp]='Astr8',1,0)+IIf([S_3.AstrGrp]='Astr8',1,0)+IIf([S_4.AstrGrp]='Astr8',1,0)+IIf([S_5.AstrGrp]='Astr8',1,0) AS Astro8, IIf([S.AstrGrp]='Astr12',1,0)+IIf([S_1.AstrGrp]='Astr12',1,0)+IIf([S_2.AstrGrp]='Astr12',1,0)+IIf([S_3.AstrGrp]='Astr12',1,0)+IIf([S_4.AstrGrp]='Astr12',1,0)+IIf([S_5.AstrGrp]='Astr12',1,0) AS Astro12, IIf([S.AstrGrp]='Others',1,0)+IIf([S_1.AstrGrp]='Others',1,0)+IIf([S_2.AstrGrp]='Others',1,0)+IIf([S_3.AstrGrp]='Others',1,0)+IIf([S_4.AstrGrp]='Others',1,0)+IIf([S_5.AstrGrp]='Others',1,0)+IIf([S.AstrGrp]='Astr12',1,0)+IIf([S_1.AstrGrp]='Astr12',1,0)+IIf([S_2.AstrGrp]='Astr12',1,0)+IIf([S_3.AstrGrp]='Astr12',1,0)+IIf([S_4.AstrGrp]='Astr12',1,0)+IIf([S_5.AstrGrp]='Astr12',1,0) AS AstroOth, IIf([S.AstrGrp]='Others',1,0)+IIf([S_1.AstrGrp]='Others',1,0)+IIf([S_2.AstrGrp]='Others',1,0)+IIf([S_3.AstrGrp]='Others',1,0)+IIf([S_4.AstrGrp]='Others',1,0)+IIf([S_5.AstrGrp]='Others',1,0) AS Others, Val([ClhtLowSkp] & [Clht6-9Skp] & [ClhtCOLD]) AS ClhtSkipcount, IIf([S.Clusthtskp] Between 0 And 5,1,0)+IIf([S_1.Clusthtskp] Between 0 And 5,1,0)+IIf([S_2.Clusthtskp] Between 0 And 5,1,0)+IIf([S_3.Clusthtskp] Between 0 And 5,1,0)+IIf([S_4.Clusthtskp] Between 0 And 5,1,0)+IIf([S_5.Clusthtskp] Between 0 And 5,1,0) AS ClhtLowSkp, IIf([S.Clusthtskp] Between 6 And 9,1,0)+IIf([S_1.Clusthtskp] Between 6 And 9,1,0)+IIf([S_2.Clusthtskp] Between 6 And 9,1,0)+IIf([S_3.Clusthtskp] Between 6 And 9,1,0)+IIf([S_4.Clusthtskp] Between 6 And 9,1,0)+IIf([S_5.Clusthtskp] Between 6 And 9,1,0) AS [Clht6-9Skp], IIf([S.Clusthtskp] Between 10 And 70,1,0)+IIf([S_1.Clusthtskp] Between 10 And 70,1,0)+IIf([S_2.Clusthtskp] Between 10 And 70,1,0)+IIf([S_3.Clusthtskp] Between 10 And 70,1,0)+IIf([S_4.Clusthtskp] Between 10 And 70,1,0)+IIf([S_5.Clusthtskp] Between 10 And 70,1,0) AS ClhtCOLD, Val([ClskpLowSkp] & [Clskp6-9Skp] & [ClskpCOLD]) AS ClSkpSkipcount, IIf([S.Clustskpskp] Between 0 And 5,1,0)+IIf([S_1.Clustskpskp] Between 0 And 5,1,0)+IIf([S_2.Clustskpskp] Between 0 And 5,1,0)+IIf([S_3.Clustskpskp] Between 0 And 5,1,0)+IIf([S_4.Clustskpskp] Between 0 And 5,1,0)+IIf([S_5.Clustskpskp] Between 0 And 5,1,0) AS ClskpLowSkp, IIf([S.Clustskpskp] Between 6 And 9,1,0)+IIf([S_1.Clustskpskp] Between 6 And 9,1,0)+IIf([S_2.Clustskpskp] Between 6 And 9,1,0)+IIf([S_3.Clustskpskp] Between 6 And 9,1,0)+IIf([S_4.Clustskpskp] Between 6 And 9,1,0)+IIf([S_5.Clustskpskp] Between 6 And 9,1,0) AS [Clskp6-9Skp], IIf([S.Clustskpskp] Between 10 And 70,1,0)+IIf([S_1.Clustskpskp] Between 10 And 70,1,0)+IIf([S_2.Clustskpskp] Between 10 And 70,1,0)+IIf([S_3.Clustskpskp] Between 10 And 70,1,0)+IIf([S_4.Clustskpskp] Between 10 And 70,1,0)+IIf([S_5.Clustskpskp] Between 10 And 70,1,0) AS ClskpCOLD, [Win2]-[Win1]-1 AS [W2-1], [Win3]-[Win2]-1 AS [W3-2], [Win4]-[Win3]-1 AS [W4-3], [Win5]-[Win4]-1 AS [W5-4], [Win6]-[Win5]-1 AS [W6-5], Val(Maxoflist([W2-1],[W3-2],[W4-3],[W5-4],[W6-5])) AS HighGap, IIf([S.HIGHLOW]='Low',1,0)+IIf([S_1.HIGHLOW]='Low',1,0)+IIf([S_2.HIGHLOW]='Low',1,0)+IIf([S_3.HIGHLOW]='Low',1,0)+IIf([S_4.HIGHLOW]='Low',1,0)+IIf([S_5.HIGHLOW]='Low',1,0) AS Low, IIf([S.HIGHLOW]='High',1,0)+IIf([S_1.HIGHLOW]='High',1,0)+IIf([S_2.HIGHLOW]='High',1,0)+IIf([S_3.HIGHLOW]='High',1,0)+IIf([S_4.HIGHLOW]='High',1,0)+IIf([S_5.HIGHLOW]='High',1,0) AS High, IIf([S.ODDEVEN]='Odd',1,0)+IIf([S_1.ODDEVEN]='Odd',1,0)+IIf([S_2.ODDEVEN]='Odd',1,0)+IIf([S_3.ODDEVEN]='Odd',1,0)+IIf([S_4.ODDEVEN]='Odd',1,0)+IIf([S_5.ODDEVEN]='Odd',1,0) AS Odd, IIf([S.ODDEVEN]='Even',1,0)+IIf([S_1.ODDEVEN]='Even',1,0)+IIf([S_2.ODDEVEN]='Even',1,0)+IIf([S_3.ODDEVEN]='Even',1,0)+IIf([S_4.ODDEVEN]='Even',1,0)+IIf([S_5.ODDEVEN]='Even',1,0) AS Even, Val([Low] & [High] & [Odd] & [Even]) AS LoHiOEcount, IIf([S.Median]=2,1,0)+IIf([S_1.Median]=2,1,0)+IIf([S_2.Median]=2,1,0)+IIf([S_3.Median]=2,1,0)+IIf([S_4.Median]=2,1,0)+IIf([S_5.Median]=2,1,0) AS Median2, IIf([S.Median]=3,1,0)+IIf([S_1.Median]=3,1,0)+IIf([S_2.Median]=3,1,0)+IIf([S_3.Median]=3,1,0)+IIf([S_4.Median]=3,1,0)+IIf([S_5.Median]=3,1,0) AS Median3, IIf([S.Median]=4,1,0)+IIf([S_1.Median]=4,1,0)+IIf([S_2.Median]=4,1,0)+IIf([S_3.Median]=4,1,0)+IIf([S_4.Median]=4,1,0)+IIf([S_5.Median]=4,1,0) AS Median4, Val([Median2] & [Median3] & [Median4]) AS Mdngrpcount, Val([Stdevskp4] & [Stdevskp5] & [Stdevskp6]) AS Stdevskpcount, IIf([S.StddevSkp]=4,1,0)+IIf([S_1.StddevSkp]=4,1,0)+IIf([S_2.StddevSkp]=4,1,0)+IIf([S_3.StddevSkp]=4,1,0)+IIf([S_4.StddevSkp]=4,1,0)+IIf([S_5.StddevSkp]=4,1,0) AS Stdevskp4, IIf([S.StddevSkp]=5,1,0)+IIf([S_1.StddevSkp]=5,1,0)+IIf([S_2.StddevSkp]=5,1,0)+IIf([S_3.StddevSkp]=5,1,0)+IIf([S_4.StddevSkp]=5,1,0)+IIf([S_5.StddevSkp]=5,1,0) AS Stdevskp5, IIf([S.StddevSkp]=6,1,0)+IIf([S_1.StddevSkp]=6,1,0)+IIf([S_2.StddevSkp]=6,1,0)+IIf([S_3.StddevSkp]=6,1,0)+IIf([S_4.StddevSkp]=6,1,0)+IIf([S_5.StddevSkp]=6,1,0) AS Stdevskp6, IIf([S.10GmHit]=0,1,0)+IIf([S_1.10GmHit]=0,1,0)+IIf([S_2.10GmHit]=0,1,0)+IIf([S_3.10GmHit]=0,1,0)+IIf([S_4.10GmHit]=0,1,0)+IIf([S_5.10GmHit]=0,1,0) AS Hit0, IIf([S.10GmHit]=1,1,0)+IIf([S_1.10GmHit]=1,1,0)+IIf([S_2.10GmHit]=1,1,0)+IIf([S_3.10GmHit]=1,1,0)+IIf([S_4.10GmHit]=1,1,0)+IIf([S_5.10GmHit]=1,1,0) AS Hit1, IIf([S.10GmHit]=2,1,0)+IIf([S_1.10GmHit]=2,1,0)+IIf([S_2.10GmHit]=2,1,0)+IIf([S_3.10GmHit]=2,1,0)+IIf([S_4.10GmHit]=2,1,0)+IIf([S_5.10GmHit]=2,1,0) AS Hit2, IIf([S.10GmHit]=3,1,0)+IIf([S_1.10GmHit]=3,1,0)+IIf([S_2.10GmHit]=3,1,0)+IIf([S_3.10GmHit]=3,1,0)+IIf([S_4.10GmHit]=3,1,0)+IIf([S_5.10GmHit]=3,1,0) AS Hit3, IIf([S.10GmHit]=4,1,0)+IIf([S_1.10GmHit]=4,1,0)+IIf([S_2.10GmHit]=4,1,0)+IIf([S_3.10GmHit]=4,1,0)+IIf([S_4.10GmHit]=4,1,0)+IIf([S_5.10GmHit]=4,1,0) AS Hit4, IIf([S.10GmHit] Between 3 And 6,1,0)+IIf([S_1.10GmHit] Between 3 And 6,1,0)+IIf([S_2.10GmHit] Between 3 And 6,1,0)+IIf([S_3.10GmHit] Between 3 And 6,1,0)+IIf([S_4.10GmHit] Between 3 And 6,1,0)+IIf([S_5.10GmHit] Between 3 And 6,1,0) AS Hitother, Val([Hit0] & [Hit1] & [Hit2] & [Hit3] & [Hit4]) AS 10gmhitcount, Val([Hit0] & [Hit1] & [Hit2] & [Hitother]) AS 10gmhitcount2, IIf([S.Clusththt]=2,1,0)+IIf([S_1.Clusththt]=2,1,0)+IIf([S_2.Clusththt]=2,1,0)+IIf([S_3.Clusththt]=2,1,0)+IIf([S_4.Clusththt]=2,1,0)+IIf([S_5.Clusththt]=2,1,0) AS Clusterhtht2, IIf([S.Clusththt]=3,1,0)+IIf([S_1.Clusththt]=3,1,0)+IIf([S_2.Clusththt]=3,1,0)+IIf([S_3.Clusththt]=3,1,0)+IIf([S_4.Clusththt]=3,1,0)+IIf([S_5.Clusththt]=3,1,0) AS Clusterhtht3, IIf([S.Clusththt]=4,1,0)+IIf([S_1.Clusththt]=4,1,0)+IIf([S_2.Clusththt]=4,1,0)+IIf([S_3.Clusththt]=4,1,0)+IIf([S_4.Clusththt]=4,1,0)+IIf([S_5.Clusththt]=4,1,0) AS Clusterhtht4, IIf([S.Clusththt]=5,1,0)+IIf([S_1.Clusththt]=5,1,0)+IIf([S_2.Clusththt]=5,1,0)+IIf([S_3.Clusththt]=5,1,0)+IIf([S_4.Clusththt]=5,1,0)+IIf([S_5.Clusththt]=5,1,0) AS Clusterhtht5, Val([Clusterhtht2] & [Clusterhtht3] & [Clusterhtht4] & [Clusterhtht5]) AS Clusththtcount, IIf([S.Clustskpht]=2,1,0)+IIf([S_1.Clustskpht]=2,1,0)+IIf([S_2.Clustskpht]=2,1,0)+IIf([S_3.Clustskpht]=2,1,0)+IIf([S_4.Clustskpht]=2,1,0)+IIf([S_5.Clustskpht]=2,1,0) AS Clusterskpht2, IIf([S.Clustskpht]=3,1,0)+IIf([S_1.Clustskpht]=3,1,0)+IIf([S_2.Clustskpht]=3,1,0)+IIf([S_3.Clustskpht]=3,1,0)+IIf([S_4.Clustskpht]=3,1,0)+IIf([S_5.Clustskpht]=3,1,0) AS Clusterskpht3, IIf([S.Clustskpht]=4,1,0)+IIf([S_1.Clustskpht]=4,1,0)+IIf([S_2.Clustskpht]=4,1,0)+IIf([S_3.Clustskpht]=4,1,0)+IIf([S_4.Clustskpht]=4,1,0)+IIf([S_5.Clustskpht]=4,1,0) AS Clusterskpht4, IIf([S.Clustskpht]=5,1,0)+IIf([S_1.Clustskpht]=5,1,0)+IIf([S_2.Clustskpht]=5,1,0)+IIf([S_3.Clustskpht]=5,1,0)+IIf([S_4.Clustskpht]=5,1,0)+IIf([S_5.Clustskpht]=5,1,0) AS Clusterskpht5, Val([Clusterskpht2] & [Clusterskpht3] & [Clusterskpht4] & [Clusterskpht5]) AS Clustskphtcount, IIf([S.SkipGrp]='LowSkip',1,0)+IIf([S_1.SkipGrp]='LowSkip',1,0)+IIf([S_2.SkipGrp]='LowSkip',1,0)+IIf([S_3.SkipGrp]='LowSkip',1,0)+IIf([S_4.SkipGrp]='LowSkip',1,0)+IIf([S_5.SkipGrp]='LowSkip',1,0) AS LowSkp, IIf([S.SkipGrp]='6-9',1,0)+IIf([S_1.SkipGrp]='6-9',1,0)+IIf([S_2.SkipGrp]='6-9',1,0)+IIf([S_3.SkipGrp]='6-9',1,0)+IIf([S_4.SkipGrp]='6-9',1,0)+IIf([S_5.SkipGrp]='6-9',1,0) AS [6-9Skp], IIf([S.SkipGrp]='COLD',1,0)+IIf([S_1.SkipGrp]='COLD',1,0)+IIf([S_2.SkipGrp]='COLD',1,0)+IIf([S_3.SkipGrp]='COLD',1,0)+IIf([S_4.SkipGrp]='COLD',1,0)+IIf([S_5.SkipGrp]='COLD',1,0) AS COLD, Val([LowSkp] & [6-9Skp] & [COLD]) AS Skipcount, IIf([S.OAvgGrp]='Zer0',1,0)+IIf([S_1.OAvgGrp]='Zer0',1,0)+IIf([S_2.OAvgGrp]='Zer0',1,0)+IIf([S_3.OAvgGrp]='Zer0',1,0)+IIf([S_4.OAvgGrp]='Zer0',1,0)+IIf([S_5.OAvgGrp]='Zer0',1,0) AS OutAvg0, IIf([S.OAvgGrp]='Two',1,0)+IIf([S_1.OAvgGrp]='Two',1,0)+IIf([S_2.OAvgGrp]='Two',1,0)+IIf([S_3.OAvgGrp]='Two',1,0)+IIf([S_4.OAvgGrp]='Two',1,0)+IIf([S_5.OAvgGrp]='Two',1,0) AS OutAvg2, IIf([S.OAvgGrp]='Other',1,0)+IIf([S_1.OAvgGrp]='Other',1,0)+IIf([S_2.OAvgGrp]='Other',1,0)+IIf([S_3.OAvgGrp]='Other',1,0)+IIf([S_4.OAvgGrp]='Other',1,0)+IIf([S_5.OAvgGrp]='Other',1,0) AS OutAvgother, Val([OutAvg0] & [OutAvg2] & [OutAvgother]) AS OutAvgcount, IIf([S.LskpGrp]='Lastzero',1,0)+IIf([S_1.LskpGrp]='Lastzero',1,0)+IIf([S_2.LskpGrp]='Lastzero',1,0)+IIf([S_3.LskpGrp]='Lastzero',1,0)+IIf([S_4.LskpGrp]='Lastzero',1,0)+IIf([S_5.LskpGrp]='Lastzero',1,0) AS Lskp0, IIf([S.LskpGrp]='Lastone',1,0)+IIf([S_1.LskpGrp]='Lastone',1,0)+IIf([S_2.LskpGrp]='Lastone',1,0)+IIf([S_3.LskpGrp]='Lastone',1,0)+IIf([S_4.LskpGrp]='Lastone',1,0)+IIf([S_5.LskpGrp]='Lastone',1,0) AS Lskp1, IIf([S.LskpGrp]='Other',1,0)+IIf([S_1.LskpGrp]='Other',1,0)+IIf([S_2.LskpGrp]='Other',1,0)+IIf([S_3.LskpGrp]='Other',1,0)+IIf([S_4.LskpGrp]='Other',1,0)+IIf([S_5.LskpGrp]='Other',1,0) AS Lskpother, Val([Lskp0] & [Lskp1] & [Lskpother]) AS LastSkpcount, Val([7High] & [7Low] & [OtherHit]) AS Hitratiocount, IIf([S.HitGrp]='7high',1,0)+IIf([S_1.HitGrp]='7high',1,0)+IIf([S_2.HitGrp]='7high',1,0)+IIf([S_3.HitGrp]='7high',1,0)+IIf([S_4.HitGrp]='7high',1,0)+IIf([S_5.HitGrp]='7high',1,0) AS 7High, IIf([S.HitGrp]='7low',1,0)+IIf([S_1.HitGrp]='7low',1,0)+IIf([S_2.HitGrp]='7low',1,0)+IIf([S_3.HitGrp]='7low',1,0)+IIf([S_4.HitGrp]='7low',1,0)+IIf([S_5.HitGrp]='7low',1,0) AS 7Low, IIf([S.HitGrp]='Other',1,0)+IIf([S_1.HitGrp]='Other',1,0)+IIf([S_2.HitGrp]='Other',1,0)+IIf([S_3.HitGrp]='Other',1,0)+IIf([S_4.HitGrp]='Other',1,0)+IIf([S_5.HitGrp]='Other',1,0) AS OtherHit, IIf([S.AvgSkGrp]='FourFour',1,0)+IIf([S_1.AvgSkGrp]='FourFour',1,0)+IIf([S_2.AvgSkGrp]='FourFour',1,0)+IIf([S_3.AvgSkGrp]='FourFour',1,0)+IIf([S_4.AvgSkGrp]='FourFour',1,0)+IIf([S_5.AvgSkGrp]='FourFour',1,0) AS Avgskp44, IIf([S.AvgSkGrp]='FourFive',1,0)+IIf([S_1.AvgSkGrp]='FourFive',1,0)+IIf([S_2.AvgSkGrp]='FourFive',1,0)+IIf([S_3.AvgSkGrp]='FourFive',1,0)+IIf([S_4.AvgSkGrp]='FourFive',1,0)+IIf([S_5.AvgSkGrp]='FourFive',1,0) AS Avgskp45, IIf([S.AvgSkGrp]='FourSix',1,0)+IIf([S_1.AvgSkGrp]='FourSix',1,0)+IIf([S_2.AvgSkGrp]='FourSix',1,0)+IIf([S_3.AvgSkGrp]='FourSix',1,0)+IIf([S_4.AvgSkGrp]='FourSix',1,0)+IIf([S_5.AvgSkGrp]='FourSix',1,0) AS Avgskp46, IIf([S.AvgSkGrp]='Others',1,0)+IIf([S_1.AvgSkGrp]='Others',1,0)+IIf([S_2.AvgSkGrp]='Others',1,0)+IIf([S_3.AvgSkGrp]='Others',1,0)+IIf([S_4.AvgSkGrp]='Others',1,0)+IIf([S_5.AvgSkGrp]='Others',1,0) AS Avgskpother, IIf([S.AvgSkGrp]='Others',1,0)+IIf([S_1.AvgSkGrp]='Others',1,0)+IIf([S_2.AvgSkGrp]='Others',1,0)+IIf([S_3.AvgSkGrp]='Others',1,0)+IIf([S_4.AvgSkGrp]='Others',1,0)+IIf([S_5.AvgSkGrp]='Others',1,0)+IIf([S.AvgSkGrp]='FourSix',1,0)+IIf([S_1.AvgSkGrp]='FourSix',1,0)+IIf([S_2.AvgSkGrp]='FourSix',1,0)+IIf([S_3.AvgSkGrp]='FourSix',1,0)+IIf([S_4.AvgSkGrp]='FourSix',1,0)+IIf([S_5.AvgSkGrp]='FourSix',1,0) AS Avgskpot2, Val([Avgskp44] & [Avgskp45] & [Avgskp46] & [Avgskpother]) AS AvgSkpcount, Val([Avgskp44] & [Avgskp45] & [Avgskpot2]) AS AvgSkpcount2, IIf([S.LngskGrp]='Twoone',1,0)+IIf([S_1.LngskGrp]='Twoone',1,0)+IIf([S_2.LngskGrp]='Twoone',1,0)+IIf([S_3.LngskGrp]='Twoone',1,0)+IIf([S_4.LngskGrp]='Twoone',1,0)+IIf([S_5.LngskGrp]='Twoone',1,0) AS Lngskp21, IIf([S.LngskGrp]='Twotwo',1,0)+IIf([S_1.LngskGrp]='Twotwo',1,0)+IIf([S_2.LngskGrp]='Twotwo',1,0)+IIf([S_3.LngskGrp]='Twotwo',1,0)+IIf([S_4.LngskGrp]='Twotwo',1,0)+IIf([S_5.LngskGrp]='Twotwo',1,0) AS Lngskp22, IIf([S.LngskGrp]='Twothree',1,0)+IIf([S_1.LngskGrp]='Twothree',1,0)+IIf([S_2.LngskGrp]='Twothree',1,0)+IIf([S_3.LngskGrp]='Twothree',1,0)+IIf([S_4.LngskGrp]='Twothree',1,0)+IIf([S_5.LngskGrp]='Twothree',1,0) AS Lngskp23, IIf([S.LngskGrp]='Other',1,0)+IIf([S_1.LngskGrp]='Other',1,0)+IIf([S_2.LngskGrp]='Other',1,0)+IIf([S_3.LngskGrp]='Other',1,0)+IIf([S_4.LngskGrp]='Other',1,0)+IIf([S_5.LngskGrp]='Other',1,0) AS Lngskpoth, IIf([S.LngskGrp]='Other',1,0)+IIf([S_1.LngskGrp]='Other',1,0)+IIf([S_2.LngskGrp]='Other',1,0)+IIf([S_3.LngskGrp]='Other',1,0)+IIf([S_4.LngskGrp]='Other',1,0)+IIf([S_5.LngskGrp]='Other',1,0)+IIf([S.LngskGrp]='Twothree',1,0)+IIf([S_1.LngskGrp]='Twothree',1,0)+IIf([S_2.LngskGrp]='Twothree',1,0)+IIf([S_3.LngskGrp]='Twothree',1,0)+IIf([S_4.LngskGrp]='Twothree',1,0)+IIf([S_5.LngskGrp]='Twothree',1,0) AS Lngskpot2, Val([Lngskp21] & [Lngskp22] & [Lngskp23] & [Lngskpoth]) AS LngSkpcount, Val([Lngskp21] & [Lngskp22] & [Lngskpot2]) AS LngSkpcount2, IIf([Win2]-[Win1]=1,1,0) AS Nhb1, IIf([Win3]-[Win2]=1,1,0) AS Nhb2, IIf([Win4]-[Win3]=1,1,0) AS Nhb3, IIf([Win5]-[Win4]=1,1,0) AS Nhb4, IIf([Win6]-[Win5]=1,1,0)+IIf([Win6]-[Win1]=38,1,0) AS Nhb5, IIf([Nhb1]=0 And [Nhb2]=0 And [Nhb3]=0 And [Nhb4]=0 And [Nhb5]=0,0,0) AS Noneighb, IIf([Nhb1]=1 And [Nhb2]=0 And [Nhb3]=1 And [Nhb4]=0 And [Nhb5]=1,222,0) AS 222Neighb, IIf([Nhb1]=1 And [Nhb2]=0 And [Nhb3]=1 And [Nhb4]=0 And [Nhb5]=0,22,0)+IIf([Nhb1]=0 And [Nhb2]=1 And [Nhb3]=0 And [Nhb4]=1 And [Nhb5]=0,22,0)+IIf([Nhb1]=0 And [Nhb2]=0 And [Nhb3]=1 And [Nhb4]=0 And [Nhb5]=1,22,0)+IIf([Nhb1]=1 And [Nhb2]=0 And [Nhb3]=0 And [Nhb4]=1 And [Nhb5]=0,22,0)+IIf([Nhb1]=1 And [Nhb2]=0 And [Nhb3]=0 And [Nhb4]=0 And [Nhb5]=1,22,0)+IIf([Nhb1]=0 And [Nhb2]=1 And [Nhb3]=0 And [Nhb4]=0 And [Nhb5]=1,22,0) AS 22Neighb, IIf([Nhb1]=1 And [Nhb2]=0 And [Nhb3]=0 And [Nhb4]=0 And [Nhb5]=0,2,0)+IIf([Nhb1]=0 And [Nhb2]=1 And [Nhb3]=0 And [Nhb4]=0 And [Nhb5]=0,2,0)+IIf([Nhb1]=0 And [Nhb2]=0 And [Nhb3]=1 And [Nhb4]=0 And [Nhb5]=0,2,0)+IIf([Nhb1]=0 And [Nhb2]=0 And [Nhb3]=0 And [Nhb4]=1 And [Nhb5]=0,2,0)+IIf([Nhb1]=0 And [Nhb2]=0 And [Nhb3]=0 And [Nhb4]=0 And [Nhb5]=1,2,0) AS 2Neighb, IIf([Nhb1]=1 And [Nhb2]=1 And [Nhb3]=0 And [Nhb4]=1 And [Nhb5]=0,32,0)+IIf([Nhb1]=0 And [Nhb2]=1 And [Nhb3]=1 And [Nhb4]=0 And [Nhb5]=1,32,0)+IIf([Nhb1]=1 And [Nhb2]=1 And [Nhb3]=0 And [Nhb4]=0 And [Nhb5]=1,32,0)+IIf([Nhb1]=1 And [Nhb2]=0 And [Nhb3]=1 And [Nhb4]=1 And [Nhb5]=0,32,0)+IIf([Nhb1]=1 And [Nhb2]=0 And [Nhb3]=0 And [Nhb4]=1 And [Nhb5]=1,32,0)+IIf([Nhb1]=0 And [Nhb2]=1 And [Nhb3]=0 And [Nhb4]=1 And [Nhb5]=1,32,0) AS 32Neighb, IIf([Nhb1]=1 And [Nhb2]=1 And [Nhb3]=0 And [Nhb4]=0 And [Nhb5]=0,3,0)+IIf([Nhb1]=0 And [Nhb2]=1 And [Nhb3]=1 And [Nhb4]=0 And [Nhb5]=0,3,0)+IIf([Nhb1]=0 And [Nhb2]=0 And [Nhb3]=1 And [Nhb4]=1 And [Nhb5]=0,3,0)+IIf([Nhb1]=0 And [Nhb2]=0 And [Nhb3]=0 And [Nhb4]=1 And [Nhb5]=1,3,0)+IIf([Nhb1]=0 And [Nhb2]=0 And [Nhb3]=0 And [Nhb4]=0 And [Nhb5]=2,3,0) AS 3Neighb, IIf([Nhb1]=1 And [Nhb2]=1 And [Nhb3]=1 And [Nhb4]=0 And [Nhb5]=1,42,0)+IIf([Nhb1]=1 And [Nhb2]=0 And [Nhb3]=1 And [Nhb4]=1 And [Nhb5]=1,42,0) AS [4+2Neighb], IIf([Nhb1]=1 And [Nhb2]=1 And [Nhb3]=1 And [Nhb4]=0 And [Nhb5]=0,4,0)+IIf([Nhb1]=0 And [Nhb2]=1 And [Nhb3]=1 And [Nhb4]=1 And [Nhb5]=0,4,0)+IIf([Nhb1]=0 And [Nhb2]=0 And [Nhb3]=1 And [Nhb4]=1 And [Nhb5]=1,4,0) AS 4Neighb, IIf([Nhb1]=1 And [Nhb2]=1 And [Nhb3]=1 And [Nhb4]=1 And [Nhb5]=0,5,0)+IIf([Nhb1]=0 And [Nhb2]=1 And [Nhb3]=1 And [Nhb4]=1 And [Nhb5]=1,5,0) AS 5Neighb, IIf([Nhb1]=1 And [Nhb2]=1 And [Nhb3]=1 And [Nhb4]=1 And [Nhb5]=1,6,0) AS 6Neighb, IIf([Noneighb]=5,0,0)+IIf([222Neighb]=222,222,0)+IIf([22Neighb]=22,22,0)+IIf([2Neighb]=2,2,0)+IIf([32Neighb]=32,32,0)+IIf([3Neighb]=3,3,0)+IIf([4+2Neighb]=42,42,0)+IIf([4Neighb]=4,4,0)+IIf([5Neighb]=5,5,0)+IIf([6Neighb]=6,6,0) AS NhbCount INTO GreatExperimentFinal
FROM ((((((DM LEFT JOIN DMSkips AS S ON DM.Win1 = S.Number) LEFT JOIN DMSkips AS S1 ON DM.Win2 = S1.Number) LEFT JOIN DMSkips AS S2 ON DM.Win3 = S2.Number) LEFT JOIN DMSkips AS S3 ON DM.Win4 = S3.Number) LEFT JOIN DMSkips AS S4 ON DM.Win5 = S4.Number) LEFT JOIN DMSkips AS S5 ON DM.Win6 = S5.Number) LEFT JOIN GameSumCountRank ON DM.Sum = GameSumCountRank.Sum
WHERE ((([Win6]-[Win1]+1)<>34));
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, 20:08
Subject: Re: [MS_AccessPros] QUERY TOO COMPLEX
Ade-
Like this:
FROM ((((((DM LEFT JOIN DMSkips AS S ON DM.Win1 = S.Number) LEFT JOIN DMSkips AS S1 ON DM.Win2 = S1.Number) LEFT JOIN DMSkips AS S2 ON DM.Win3 = S2.Number) LEFT JOIN DMSkips AS S3 ON DM.Win4 = S3.Number) LEFT JOIN DMSkips AS S4 ON DM.Win5 = S4.Number) LEFT JOIN DMSkips AS S5 ON DM.Win6 = S5.Number) LEFT JOIN GameSumCountRank ON DM.Sum = GameSumCountRank.Sum
If your SELECT or WHERE or ORDER BY clauses refer to any field in DMSkips_n, then you'll need to change those as well from DMSkips_n to Sn, where n matches the number in the FROM clause.
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 11:37 AM, Adeboyejo Oyenuga aoye_99@yahoo.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Thanks Bill I have no problems with changing the SELECT part of the statement. It is the FROM i am concerned with. I know I need to indicate that it should recognise S1 as DMSkips but what I did did not work. Kindly take a look at the original FROM statement
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: "wrmosca@comcast.net [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, 16 August 2016, 19:23
Subject: Re: [MS_AccessPros] QUERY TOO COMPLEX
Ade
Once you declare an alias you should use it in every spot where the table name is used like this:
SELECT S1.LastName, S1.FirstName
FROM myTable As S1
WHERE S1.LastName LIKE "SMI*"
Not only does it shorten the query but it's easier to write.
Regards,
Bill Mosca, Founder - MS_Access_Professionals
My nothing-to-do-with-Access blog
---In MS_Access_Professionals@yahoogroups.com, <aoye_99@yahoo.co.uk> wrote :
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];
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
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 (22) |
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:
Posting Komentar