Results 1 to 4 of 4
  1. #1
    JohnWood is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2023
    Posts
    2

    Running Query, but can NOT Make Table

    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?

  2. #2
    madpiet is offline Expert
    Windows 10 Office 365
    Join Date
    Feb 2023
    Posts
    566
    Can't help but wonder about the underlying table structure(s).

    Also wonder if it wouldn't be better to do all the casting of data types in the lowest level query and then build on top of that.

  3. #3
    pbleeds is offline Novice
    Windows 11 Access 2016
    Join Date
    Jan 2025
    Posts
    1
    Hi.
    If the query you have displayed does run, then you could:
    1. run your query
    2. right-click and copy the query output
    3. click 'create' tab, then 'table'
    4. right-click in top left corner in table design, and select paste

    This should allow you to create a table with the data from your query.

  4. #4
    JohnWood is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2023
    Posts
    2

    Query runs, but can NOT make table

    Quote Originally Posted by pbleeds View Post
    Hi.
    If the query you have displayed does run, then you could:
    1. run your query
    2. right-click and copy the query output
    3. click 'create' tab, then 'table'
    4. right-click in top left corner in table design, and select paste

    This should allow you to create a table with the data from your query.
    Thank you very much!
    Works too easy.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 06-05-2020, 02:09 PM
  2. Replies: 4
    Last Post: 04-07-2018, 11:55 PM
  3. Replies: 2
    Last Post: 02-17-2015, 01:01 PM
  4. Replies: 4
    Last Post: 12-16-2014, 05:16 AM
  5. Running queries, VIA VB, but not running in Order
    By mike02 in forum Programming
    Replies: 6
    Last Post: 06-01-2013, 07:07 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums