Help! I'm beginning to wonder about my sanity!
I am trying to find an overall average of point scores given to a class for a certain subject, during a certain assessment period.
I have created a query using 2 tables;
TblYearData contains information about a given child in a given school year. For this query I am using the YearDataID, the ClassID and the SchoolYear fields.
TblGrades contains 5 fields; GradeID, YearDataID, SubjectID, AssessmentID, and Points. This is indexed so there can only be one record with the same YearDataID, SubjectID, and AssessmentID.
So - the query.
The field YearDataID had the following criteria;
In (SELECT [YearDataID] FROM [TblYearData] WHERE [SchoolYear]=[Forms].[FormViewData].[SchoolYear] AND[ClassID]=[Forms].[FormViewData].[ClassID])
The SubjectID field has the criteria '=3' (i.e. Reading)
The AssessmentID field has the criteria '=2' (i.e Autumn)
The final field in the query is 'Points'. I have set this to 'Avg', and ideally I want the query to return just 1 value - an average across all the records. However, currently, the query just returns all the records and doesn't seem to average them at all.
I'm sure there is something simple I've missed again - any help would be greatly appreciate!
I am trying to find an overall average of point scores given to a class for a certain subject, during a certain assessment period.
I have created a query using 2 tables;
TblYearData contains information about a given child in a given school year. For this query I am using the YearDataID, the ClassID and the SchoolYear fields.
TblGrades contains 5 fields; GradeID, YearDataID, SubjectID, AssessmentID, and Points. This is indexed so there can only be one record with the same YearDataID, SubjectID, and AssessmentID.
So - the query.
The field YearDataID had the following criteria;
In (SELECT [YearDataID] FROM [TblYearData] WHERE [SchoolYear]=[Forms].[FormViewData].[SchoolYear] AND[ClassID]=[Forms].[FormViewData].[ClassID])
The SubjectID field has the criteria '=3' (i.e. Reading)
The AssessmentID field has the criteria '=2' (i.e Autumn)
The final field in the query is 'Points'. I have set this to 'Avg', and ideally I want the query to return just 1 value - an average across all the records. However, currently, the query just returns all the records and doesn't seem to average them at all.
I'm sure there is something simple I've missed again - any help would be greatly appreciate!