1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

access 2007: query too complex

Discussion in 'All Other Software' started by kary, Apr 28, 2010.

Thread Status:
Not open for further replies.
  1. kary

    kary Thread Starter

    Apr 28, 2010
    I am working in Windows 7, Access 2007.

    I store grades in one table called "OverallResults" and student details are also in a table (StudentDetails).

    I have created a series of six queries (aModule x Average) to calculate the results for modules 1 - 6. (Each of these queries works individually) Each module consists of many assignments and I have calculated the average per assignment and module in these queries. However, when I combine all these queries to get an overall average, it produces the error message "Query too complex".

    The Sql code in the average query is:

    SELECT StudentsDetails.Country, StudentsDetails.MentorGroupName, StudentsDetails.OrganizationName, StudentsDetails.Cycle, [aModule 1 Marks].M1Final1, [aModule 1 Marks].M1Final2, [aModule 1 Marks].M1Avg, [aModule 2 Marks].M2Final1, [aModule 2 Marks].M2Final2, [aModule 2 Marks].M2Avg, [aModule 3 Marks].M3Final1, [aModule 3 Marks].M3Final2, [aModule 3 Marks].M3Avg, [aModule 4 Marks].M4Final1, [aModule 4 Marks].M4Final2, [aModule 4 Marks].M4Avg, [aModule 5 Marks].M5Final1, [aModule 5 Marks].M5Final2, [aModule 5 Marks].M5Avg, [aModule 6 Marks].M6Final1, [aModule 6 Marks].M6Final2, [aModule 6 Marks].M6Avg, IIf([M6Avg]+[M5Avg]+[M4Avg]+[M3Avg]+[M2Avg]+[M1Avg] Is Null,Null,Round(([M1Avg]+[M2Avg]+[M3Avg]+[M4Avg]+[M5Avg]+[M6Avg])/6,2)) AS [Course Avg3]
    FROM (((((StudentsDetails INNER JOIN [aModule 1 Marks] ON StudentsDetails.StudentNo = [aModule 1 Marks].StudentNo) INNER JOIN [aModule 2 Marks] ON StudentsDetails.StudentNo = [aModule 2 Marks].StudentNo) INNER JOIN [aModule 3 Marks] ON StudentsDetails.StudentNo = [aModule 3 Marks].StudentNo) INNER JOIN [aModule 4 Marks] ON StudentsDetails.StudentNo = [aModule 4 Marks].StudentNo) INNER JOIN [aModule 5 Marks] ON StudentsDetails.StudentNo = [aModule 5 Marks].StudentNo) INNER JOIN [aModule 6 Marks] ON StudentsDetails.StudentNo = [aModule 6 Marks].StudentNo;

    If I remove one of the queries it does work so I guess there are too many joins? Is there any other way I can do this with code instead?

    The reason for the Null values is mainly because I don't want an average calculated if they haven't submitted an assignment.

    Perhaps I should add that this used to work but I have recently added the fields "Withdrawn" and "Cycle" to my table and the queries. SInce then this Average query hasn't worked.

    Many thanks
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/919716

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice