Query is too complex

Question

I am working with basketball stats and ACCESS 2013.  I am trying to find the total for a 10 game span.  It takes me 2 queries to find the number of rebounds for each game.  I run a query and have 1269 players with a total for the 10 games.  I can tell this running slow, but it easily shows me the totals within 2 minutes.  But the top 20 results are just 4 people with their 10 game span, just starting at a different game.  C.W. has the top 5 results whether it starts with game 6 or game 11.

This is a 'good' SQL:

SELECT

 Most_V_Reb_g10_v1_Query.Player_ID,

 Most_V_Reb_g10_v1_Query.Roster_ID,

 Most_V_Reb_g10_v1_Query.Name,

 Most_V_Reb_g10_v1_Query.g1_Rb,

 Most_V_Reb_g10_v1_Query.g2_Rb,

 Most_V_Reb_g10_v1_Query.g3_Rb,

 Most_V_Reb_g10_v1_Query.g4_Rb,

 Most_V_Reb_g10_v1_Query.g5_Rb,

 Most_V_Reb_g10_v1_Query.g6_Rb,

 Most_V_Reb_g10_v1_Query.g7_Rb,

 Most_V_Reb_g10_v1_Query.g8_Rb,

 Most_V_Reb_g10_v1_Query.g9_Rb,

 Most_V_Reb_g10_v1_Query.g10_Rb,

 Val(Nz([g1_Rb]))+Val(Nz([g2_Rb]))+Val(Nz([g3_Rb]))+Val(Nz([g4_Rb]))+Val(Nz([g5_Rb]))+Val(Nz([g6_Rb]))+Val(Nz([g7_Rb]))+Val(Nz([g8_Rb]))+Val(Nz([g9_Rb]))+Val(Nz([g10_Rb])) AS Tot, Most_V_Reb_g10_v1_Query.Game_ID,

 Most_V_Reb_g10_v1_Query.Season_ID,

 Most_V_Reb_g10_v1_Query.[Game_#],

 Most_V_Reb_g10_v1_Query.g10_Gnum

FROM Most_V_Reb_g10_v1_Query

ORDER BY Val(Nz([g1_Rb]))+Val(Nz([g2_Rb]))+Val(Nz([g3_Rb]))+Val(Nz([g4_Rb]))+Val(Nz([g5_Rb]))+Val(Nz([g6_Rb]))+Val(Nz([g7_Rb]))+Val(Nz([g8_Rb]))+Val(Nz([g9_Rb]))+Val(Nz([g10_Rb])) DESC , Most_V_Reb_g10_v1_Query.Game_ID;

When I try to allow only 1 entry per season, I then get an error:  'Query is too complex'

This is the SQL that is having problems:

SELECT DISTINCT TOP 30 A.Player_ID, A.Roster_ID, A.Name, A.Tot_Reb AS g1_Rb, B.g2_Rb, B.g3_Rb, F.g4_Rb, F.g5_Rb, F.g6_Rb, F.g7_Rb, F.g8_Rb, F.g9_Rb, F.g10_Rb, F.Tot, A.Game_ID, A.Season_ID, A.[Game_#], F.g10_Gnum

FROM ((Most_V_Reb_2g_v1_Query AS A

                   INNER JOIN [Most_V_Reb_3/4g_v2_Query] AS B ON (A.Game_ID = B.Game_ID) AND (A.Roster_ID = B.Roster_ID))

                                INNER JOIN Most_V_Reb_g10_TOT_v1_Query AS F ON (A.Game_ID = F.Game_ID) AND (A.Roster_ID = F.Roster_ID))

                                       INNER JOIN (Select Roster_ID, Max(Tot) AS MAX_TOT

                                                           FROM Most_V_Reb_g10_TOT_v1_Query GROUP BY Roster_ID) AS S

                                                                             ON (A.Roster_ID = S.Roster_ID) AND (F.Tot = S.MAX_TOT)

ORDER BY 14 DESC , 15;

I tried using Query A since that was the original that I used for the first games rebounds, and much of that data was carried thru on the 20 other queries.

I ran into the same problem when I ran everything just from F instead of using A and C, except this seemed to go further before the error showed.

Any ideas (meaning solutions) would be greatly appreciated.  I'm sorry if that appears cynical.  I appreciate ideas that help correct the problem, not somebody telling me I have a problem, or your queries should be written differently.  How should they be re-written.

I ran 5 game span and it seems to work fine.

Answer 1

I'm thinking that it is easy to 'update' my new table by just re-running my 'first' query with the totals.

Answer 2

I made my query that had the 10 games and their total into a Table, using the Make Table option for queries.  I was then able to make a new query using my new table and INNER JOIN with Roster_ID and MAX(Tot) and it ran quickly (and I believe correctly).

By making the new table, it appears to have the numbers available without going thru the extended processing that my queries were doing.

Answer 3

What do your tables look like?

Answer 4

Most_V_Reb_g10_v1_Query.g1_Rb,

Most_V_Reb_g10_v1_Query.g2_Rb,

Most_V_Reb_g10_v1_Query.g3_Rb,

Most_V_Reb_g10_v1_Query.g4_Rb,

Most_V_Reb_g10_v1_Query.g5_Rb,

Most_V_Reb_g10_v1_Query.g6_Rb,

Most_V_Reb_g10_v1_Query.g7_Rb,

Most_V_Reb_g10_v1_Query.g8_Rb,

Most_V_Reb_g10_v1_Query.g9_Rb,

Most_V_Reb_g10_v1_Query.g10_Rb,

These fields need to be separate RECORDS!   When you chain queries together Access treat them as one big & long query.

What is the structure of your base table where the data is stored?

Post the SQL of Most_V_Reb_g10_v1_Query.

Answer 5

Even saving the query takes a long time - about a minute.