access 2007: query too complex

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

kary

Thread Starter
Joined
Apr 28, 2010
Messages
2
Hi
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
Kary
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top