Averaging a query

[Reppers]

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!

 

[Reppers]

Cancel that - problem solved!

I've looked at this all day, and then 5 minutes after posting for help, I solve it myself - blooming typical.

The query was struggling with averaging the field with the subquery. I'm not sure why, I don't have the energy to find out now. I split the subquery to look for the year in one field, and the class in another, and now it works.

Ta anyway to you all!

 

[Adam Caramon]

I'd love to help you, but I'm afraid I don't know exactly what it is you're trying to accomplish.

Is this a listing of many different individual assignments that a student has completed, and then you want to determine the average of all the assignment to give a cumulative grade (and average of all the points of each assignment) per class?

I have a feeling that the structure of your 2 tables is not set up in order to accomplish what you want, but if you could answer my above question or perhaps explain in layman's terms what you're trying to do I'll try to help you.

Edit: Well, glad you got it!