Hi,
I was hoping someone could please help:
I'm trying to create a Sum on my subform in order to caclulate on my main form, but I'm getting #Error on the subform field
My Sum field control source is set as: =Sum([txtIknowLoads])
The field I'm trying to Sum is:
Name: txtIKnowLoads
Control Source: I_Know_Loads_1
The subform is based on an SQL query:
I have done a Compact & Repair on the database but to no avail
I would be very greatful for any assistance!
I was hoping someone could please help:
I'm trying to create a Sum on my subform in order to caclulate on my main form, but I'm getting #Error on the subform field
My Sum field control source is set as: =Sum([txtIknowLoads])
The field I'm trying to Sum is:
Name: txtIKnowLoads
Control Source: I_Know_Loads_1
The subform is based on an SQL query:
Code:
SELECT
firstset.Student_Name AS Name,
firstset.I_Know_Loads AS [I_Know_Loads_1],
firstset.Clinics AS Clinics,
firstset.Contraception AS Contraception,
firstset.[Viral / Bacterial] AS [Viral / Bacterial],
firstset.[Total Score] AS [Lesson 1 Score],
secondset.I_Know_Loads AS [I_Know_Loads_5],
secondset.Clinics AS [Clinics 5],
secondset.Contraception AS [Contraception 5],
secondset.[Viral / Bacterial] AS [Viral / Bacterial 5],
secondset.[Total Score] AS [Lesson 5 Score],
(secondset.[Total Score]-firstset.[Total Score]) AS Progress
FROM
(
SELECT tbl_Student.Student_ID, Student_Name, [tbl_Co-Ordinator].[Co-Ordinator_Name], I_Know_Loads, Clinics, Contraception, [Viral / Bacterial], sum([Clinics]+[Contraception]+[Viral / Bacterial])*10 AS [Total Score] FROM tbl_Student, tbl_Assessment, tbl_Year, tbl_Location, tbl_Lesson, [tbl_Co-Ordinator] WHERE tbl_Student.Student_ID = tbl_Assessment.Student_ID and tbl_Location.Location_ID = tbl_Student.Location_ID and tbl_Lesson.Lesson_ID = tbl_Assessment.Lesson_No and [tbl_Co-Ordinator].[Co-Ordinator_ID] = tbl_Assessment.[Co-Ordinator_ID] and tbl_Year.Year_ID = tbl_Assessment.Year_ID and tbl_Year.Year_Desc = [forms]![frm_View_Data]![cmbYear] and tbl_Location.Location_Name = [forms]![frm_View_Data]![cmbLocation] and tbl_Lesson.Lesson_Desc = 'Lesson 1' and [tbl_Co-Ordinator].[Co-Ordinator_Name] = [forms]![frm_View_Data]![cmbCo-Ordinator] GROUP BY tbl_Student.Student_ID, [tbl_Co-Ordinator].[Co-Ordinator_Name], Student_Name, I_Know_Loads, Clinics, Contraception, [Viral / Bacterial]
)
AS
firstset
LEFT JOIN
(
SELECT
tbl_Student.Student_ID, Student_Name, [tbl_Co-Ordinator].[Co-Ordinator_Name], I_Know_Loads, Clinics, Contraception, [Viral / Bacterial], sum([Clinics]+[Contraception]+[Viral / Bacterial])*10 AS [Total Score] FROM tbl_Student, tbl_Assessment, tbl_Year, tbl_Location, tbl_Lesson, [tbl_Co-Ordinator] WHERE tbl_Student.Student_ID = tbl_Assessment.Student_ID and tbl_Location.Location_ID = tbl_Student.Location_ID and tbl_Lesson.Lesson_ID = tbl_Assessment.Lesson_No and [tbl_Co-Ordinator].[Co-Ordinator_ID] = tbl_Assessment.[Co-Ordinator_ID] and tbl_Year.Year_ID = tbl_Assessment.Year_ID and tbl_Year.Year_Desc = [forms]![frm_View_Data]![cmbYear] and tbl_Location.Location_Name = [forms]![frm_View_Data]![cmbLocation] and tbl_Lesson.Lesson_Desc = 'Lesson 5' and [tbl_Co-Ordinator].[Co-Ordinator_Name] = [forms]![frm_View_Data]![cmbCo-Ordinator] GROUP BY tbl_student.Student_ID, [tbl_Co-Ordinator].[Co-Ordinator_Name], Student_Name, I_Know_Loads, Clinics, Contraception, [Viral / Bacterial]
)
AS
secondset
ON firstset.Student_ID = secondset.Student_ID
GROUP BY firstset.Student_Name, firstset.I_Know_Loads, firstset.Clinics, firstset.Contraception, firstset.[Viral / Bacterial], firstset.[Total Score], secondset.I_Know_Loads, secondset.Clinics, secondset.Contraception, secondset.[Viral / Bacterial], secondset.[Total Score], (secondset.[Total Score]-firstset.[Total Score]), firstset.Student_ID
ORDER BY firstset.Student_ID;I would be very greatful for any assistance!
