By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,066 Members | 1,826 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,066 IT Pros & Developers. It's quick & easy.

Error message of "Subquery can't be used in Sum(SumAtt), when I try to Sum The Sum

P: 1
I am creating a report that would generate a Summary report for Student Activity Attendance. Every student can attend multiple activities, and my
report group by student, name, and activity. Then I sum the attendance for
each individual activity, as compared to the required attendance ( I get this
from count of activity record). Next comes the Total Attendance for each student. I use Sum(SumofAttendance), Access prompt me with "Subquery can't be used in Sum of Sum" error.

I read your old discussion, and you are POSITIVE that I can "Sum the Sum".
The only difference is that this is a QUERY I use in my report.
I use Acces 2002 version.
How to FIX this error and go on?????

Below is the query---------->
SELECT [Dayly Attendance].StudentID AS Lynn, Student.LastName, Student.FirsName, [Dayly Attendance].ProgramNumber, Sum([Dayly Attendance].Attendance) AS SumAtt, Count([Dayly Attendance].ProgramNumber) AS SumSec, Sum([SumAtt]) AS SumMonAtt, Sum([SumSec]) AS SumMonSec
FROM Student INNER JOIN [Dayly Attendance] ON Student.StudentID = [Dayly Attendance].StudentID
WHERE ((([Dayly Attendance].Date) Between #3/1/2007# And #3/30/2007#))
GROUP BY [Dayly Attendance].StudentID, Student.LastName, Student.FirsName, [Dayly Attendance].ProgramNumber
ORDER BY Student.LastName, Student.FirsName, [Dayly Attendance].ProgramNumber;

THANKS for A TONS............

Mar 13 '07 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 10K+
P: 14,534
Is [Dayly Attendance] a query if so please post the SQL.

Mar 13 '07 #2

Expert Mod 15k+
P: 31,429
The error message gives the clue :
You're trying to use the Sum() function on values that are already the results of being summed. That is not supported (as it's quite illogical).
Mar 15 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.