I m using Microsoft 365 Apps for Enterprise - working in ACCESS with basketball stats.
I can make/run a query - takes a couple of minutes to run.
I want to make this a table, so that I can then query this info in a timely fashion - expecting to have several queries.
BUT when I try to Make a Table, I get repeated messages that Design view can not accept my joins.
After I ignore the 'messages' and try Making a Table, I get a message that it is too complex.
But the query does run - fairly quickly.
I'll try printing the SQL code.
SELECT G1.*, G2.[G#] AS [G2G#], Val(Nz(G2.Pts)) AS G2P, Iif(G2P>9,1,0) AS G2Y10, Iif(G2P>19,1,0) AS G2Y20, Iif(G2P>29,1,0) AS G2Y30, (G1.Y10P+G2Y10)*G1.Y10P*G2Y10 AS S2P10, (G1.Y20P+G2Y20)*G1.Y20P*G2Y20 AS S2P20, (G1.Y30P+G2Y30)*G1.Y30P*G2Y30 AS S2P30, G3.[G#] AS [G3G#], Val(Nz(G3.Pts)) AS G3P, Iif(G3P>9,1,0) AS G3Y10, Iif(G3P>19,1,0) AS G3Y20, Iif(G3P>29,1,0) AS G3Y30, Iif(S2P10 = 0, 0, iif(G3Y10=0,0,3)) AS S3P10, Iif(S2P20 = 0, 0, iif(G3Y20=0,0,3)) AS S3P20, Iif(S2P30 = 0, 0, iif(G3Y30=0,0,3)) AS S3P30, G4.[G#] AS [G4G#], Val(Nz(G4.Pts)) AS G4P, Iif(G4P>9,1,0) AS G4Y10, Iif(G4P>19,1,0) AS G4Y20, Iif(G4P>29,1,0) AS G4Y30, Iif(S3P10 = 0, 0, iif(G4Y10=0,0,4)) AS S4P10, Iif(S3P20 = 0, 0, iif(G4Y20=0,0,4)) AS S4P20, Iif(S3P30 = 0, 0, iif(G4Y30=0,0,4)) AS S4P30, G5.[G#] AS [G5G#], Val(Nz(G5.Pts)) AS G5P, Iif(G5P>9,1,0) AS G5Y10, Iif(G5P>19,1,0) AS G5Y20, Iif(G5P>29,1,0) AS G5Y30, Iif(S4P10 = 0, 0, iif(G5Y10=0,0,5)) AS S5P10, Iif(S4P20 = 0, 0, iif(G5Y20=0,0,5)) AS S5P20, Iif(S4P30 = 0, 0, iif(G5Y30=0,0,5)) AS S5P30, G1.Pts + G2P +G3P + G4P + G5P AS TP5G, G6.[G#] AS [G6G#], Val(Nz(G6.Pts)) AS G6P, Iif(G6P>9,1,0) AS G6Y10, Iif(G6P>19,1,0) AS G6Y20, Iif(S5P10 = 0, 0, iif(G6Y10=0,0,6)) AS S6P10, Iif(S5P20 = 0, 0, iif(G6Y20=0,0,6)) AS S6P20, G7.[G#] AS [G7G#], Val(Nz(G7.Pts)) AS G7P, Iif(G7P>9,1,0) AS G7Y10, Iif(G7P>19,1,0) AS G7Y20, Iif(S6P10 = 0, 0, iif(G7Y10=0,0,7)) AS S7P10, Iif(S6P20 = 0, 0, iif( G7Y20=0,0,7)) AS S7P20, G8.[G#] AS [G8G#], Val(Nz(G8.Pts)) AS G8P, Iif(G8P>9,1,0) AS G8Y10, Iif(G8P>19,1,0) AS G8Y20, Iif(S7P10 = 0, 0, iif(G8Y10=0,0,8)) AS S8P10, Iif(S7P20 = 0, 0, iif( G8Y20=0,0,8)) AS S8P20, G9.[G#] AS [G9G#], Val(Nz(G9.Pts)) AS G9P, Iif(G9P>9,1,0) AS G9Y10, Iif(G9P>19,1,0) AS G9Y20, Iif(S8P10 = 0, 0, iif(G9Y10=0,0,9)) AS S9P10, Iif(S8P20 = 0, 0, iif( G9Y20=0,0,9)) AS S9P20, G10.[G#] AS [G10G#], Val(Nz(G10.Pts)) AS G10P, Iif(G10P>9,1,0) AS G10Y10, Iif(G10P>19,1,0) AS G10Y20, Iif(S9P10 = 0, 0, iif(G10Y10=0,0,10)) AS S10P10, Iif(S9P20 = 0, 0, iif( G10Y20=0,0,10)) AS S10P20, TP5G + G6P +G7P +G8P +G9P +G10P AS TP10G
FROM (((((((([JLW_00_Test_Cons_20+Pt_Qry_v2] AS G1 LEFT JOIN [JLW_00_Test_Cons_20+Pt_Qry_v1] AS G2 ON (G1.PID = G2.PID AND G1.Fall = G2.Fall AND G1.[G#] = (G2.[G#]-1)) OR (G1.PID =G2.PID AND G1.[G#] = G1.[MxG#] and Val(Nz(G1.Fall)) = (Val(Nz(G2.Fall))-1) AND G2.[G#] =1)) LEFT JOIN [JLW_00_Test_Cons_20+Pt_Qry_v1] AS G3 ON (G1.PID = G3.PID AND G1.Fall = G3.Fall AND G1.[G#] = (G3.[G#]-2)) OR(G1.PID =G3.PID AND G1.[G#] + 1 = G1.[MxG#] and Val(Nz(G1.Fall)) = (Val(Nz(G3.Fall))-1) AND G3.[G#] =1)) LEFT JOIN [JLW_00_Test_Cons_20+Pt_Qry_v1] AS G4 ON (G1.PID = G4.PID AND G1.Fall = G4.Fall AND G1.[G#] = (G4.[G#]-3)) OR(G1.PID =G4.PID AND G1.[G#] + 2 = G1.[MxG#] and Val(Nz(G1.Fall)) = (Val(Nz(G4.Fall))-1) AND G4.[G#] =1)) LEFT JOIN [JLW_00_Test_Cons_20+Pt_Qry_v1] AS G5 ON (G1.PID = G5.PID AND G1.Fall = G5.Fall AND G1.[G#] = (G5.[G#]-4)) OR(G1.PID =G5.PID AND G1.[G#] + 3 = G1.[MxG#] and Val(Nz(G1.Fall)) = (Val(Nz(G5.Fall))-1) AND G5.[G#] =1)) LEFT JOIN [JLW_00_Test_Cons_20+Pt_Qry_v1] AS G6 ON (G1.PID = G6.PID AND G1.Fall = G6.Fall AND G1.[G#] = (G6.[G#]-5)) OR(G1.PID =G6.PID AND G1.[G#] + 4 = G1.[MxG#] and Val(Nz(G1.Fall)) = (Val(Nz(G6.Fall))-1) AND G6.[G#] =1)) LEFT JOIN [JLW_00_Test_Cons_20+Pt_Qry_v1] AS G7 ON (G1.PID = G7.PID AND G1.Fall = G7.Fall AND G1.[G#] = (G7.[G#]-6)) OR(G1.PID =G7.PID AND G1.[G#] + 5 = G1.[MxG#] and Val(Nz(G1.Fall)) = (Val(Nz(G7.Fall))-1) AND G7.[G#] =1)) LEFT JOIN [JLW_00_Test_Cons_20+Pt_Qry_v1] AS G8 ON (G1.PID = G8.PID AND G1.Fall = G8.Fall AND G1.[G#] = (G8.[G#]-7)) OR(G1.PID =G8.PID AND G1.[G#] + 6 = G1.[MxG#] and Val(Nz(G1.Fall)) = (Val(Nz(G8.Fall))-1) AND G8.[G#] =1)) LEFT JOIN [JLW_00_Test_Cons_20+Pt_Qry_v1] AS G9 ON (G1.PID = G9.PID AND G1.Fall = G9.Fall AND G1.[G#] = (G9.[G#]-8)) OR(G1.PID =G9.PID AND G1.[G#] + 7 = G1.[MxG#] and Val(Nz(G1.Fall)) = (Val(Nz(G9.Fall))-1) AND G9.[G#] =1)) LEFT JOIN [JLW_00_Test_Cons_20+Pt_Qry_v1] AS G10 ON (G1.PID = G10.PID AND G1.Fall = G10.Fall AND G1.[G#] = (G10.[G#]-9)) OR(G1.PID =G10.PID AND G1.[G#] + 8 = G1.[MxG#] and Val(Nz(G1.Fall)) = (Val(Nz(G10.Fall))-1) AND G10.[G#] =1)
ORDER BY (G1.Pts + Val(Nz(G2.Pts)) +Val(Nz(G3.Pts)) + Val(Nz(G4.Pts)) + Val(Nz(G5.Pts))+ Val(Nz(G6.Pts)) +Val(Nz(G7.Pts)) + Val(Nz(G8.Pts)) + Val(Nz(G9.Pts)) + Val(Nz(G10.Pts))) DESC , G1.Log1, G1.Fall, G1.[G#];
Any help/ideas would be appreciated. Second thought do I need to Make a Table quicker, and then Make a 2nd Table?