Unanswered: Averages from 4 fields ignoring nul or 0?
I am a music teacher in a secondary school and I have written a database for use by pupils. It holds assessment data as and when it is needed as opposed to the traditional method that relies on every pupil being ready to perform at the end of a unit of work.
The database works fine and is relatively simple. The problem comes when trying to interpret the grades as an average to display on the same form.
Jane Ayer-------6---|--8 ---|---9 -|--10--|---8.25--
Above is an example of the fields.
I need to be able to have AssAve as the average of any grades in the table but not have to work them out manually.
I am not a complete idiot , however I know very little about code so please treat me as a newbie if you reply.
the function in Access is avg(...) but I believe that it aggregates data over multiple records rather than multiple fields within a record. Given how it appears you have defined your table (more like a flat-file than a relational structure), you may have to create a calculated field in your underlying query that does the average manually.
i.e., AssAve = (Ass1 + Ass2 + Ass3 + Ass4)/4
Or, if you prefer, add some logic that divides the total by the number of non-null values in Ass1 .. Ass4.
Thanks mageem & rguy84, I will try those in 10 minutes or so.
Just another question:
How do I get it to ignore the nul values or divide by the amount of non-nul values in Ass1 - Ass4 ? This is the important bit, otherwise the pupils get a lower grade if they only do 2 than if they do 4 assessments = unhappy pupils!
Why hack at the branches when the trunk is the problem?
You need to alter your database structure. Assessments need to be in their own table, your problem will solve itself once you implement a better schema. You might want to think of a different naming scheme for your fields too
Yeah yeah I know! I have been running this database for a year without the ability to add the grades up to an average before. This is a big step and a vast improvement.
However, I am interested in what you said, any suggestions on how to get started on that route would be appreciated, although I think that needs to be on another post and anyone answering it should get paid!!! I only saw the Ass connection after I put the temp up. The database is called ARTS (Assessment Register Tracking System) and it used to be called Assessment Register System Entry, bet you can't guess the acronym for that.