Rabu, 17 Agustus 2016

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

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.

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

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

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.

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.