# Thread: Averages from 4 fields ignoring nul or 0?

1. Registered User
Join Date
Sep 2004
Posts
63

## Unanswered: Averages from 4 fields ignoring nul or 0?

Hi,

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.

e.g.
______________________________________________
Pupil----------Ass1-|-Ass2-|-Ass3-|-Ass4-|-AssAve
Joe Bloggs------4---|---7---|---3--|--nul-|---4.6---
Bert Baggins----2---|---4---|--nul-|--nul--|----3----
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.

Thanks,
ChrisOfCatford
Last edited by ChrisOfCatford; 09-01-04 at 19:10.

2. Registered User
Join Date
Jun 2004
Location
Seattle, WA
Posts
601
Make a query and put this in the field box:

AssAve: Ave(Ass1,Ass2,Ass3,Ass4)

[this is real quick thought, so it may not work...i am just about to go home ,sorry]

3. Registered User
Join Date
Nov 2003
Location
San Francisco, CA USA
Posts
59

## Average

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.

4. Registered User
Join Date
Sep 2004
Posts
63

## Many thanks, just one more thing...

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!

Thanks,
ChrisOfCatford

5. Registered User
Join Date
Sep 2004
Posts
63

## Getting there...

Sorry, Double post.
Last edited by ChrisOfCatford; 09-02-04 at 05:41.

6. Registered User
Join Date
Sep 2004
Posts
63

## Getting there...

Hi,

Having tried variants on both suggestions, I still find myself unable to get Access to accept the syntax.

I have uploaded a scaled-down version of the database to my webSpace.

Please could someone have a look at it and suggest exact syntax for the query (and anything else I may have mucked up ). Again I need an average of any grades given for Ass1, Ass2, Ass3 and Ass4.

http://www.mccartneyland.fsnet.co.uk/DB/pupils.mdb

As I said, I am no expert so if anyone manages to have look, please suggest exact syntax, otherwise I will end up hitting my head against a brick wall.

Thanks,
ChrisOfCatford

7. Registered User
Join Date
Apr 2004
Location
Sydney Australia
Posts
369
The following should work for you but I am sure there would be nicer ways of doing it.

Firstly create 4 new fields in your query

A1:IIf([Ass1]=0 Or [Ass1] Is Null,0,1)

Then do A2, A3 and A4.

Then create another field

AssCount:[A1]+[A2]+[A3]+[A4]

You then need 4 more fields to form the basis for adding up since Null values will muck things up.

AA1:IIf([Ass1] Is Null,0,[Ass1])

Then do AA2, AA3 and AA4.

Then the last field for averages will be

AssAve[AA1]+[AA2]+[AA3]+[AA4])/[AssCount]

That should give you the average for each student with 0 and Null results excluded.

The icon is appearing because of the colon followed by the bracket.

Mike
Last edited by Mike375; 09-02-04 at 09:53.

8. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
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

9. Registered User
Join Date
Sep 2004
Posts
63
Yeeeeeeeeeeeeeeeeeeeeeeeeees

Thanks Mike, you just saved my sanity!

Teddy:
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.

Thanks again for all who posted,
ChrisOfCatford

10. Registered User
Join Date
Sep 2004
Posts
63
Hello again,

Sorry to be needy, but I am now struggling to show the results in my main form. It needs to update when a record is shown in the form.

I tried a subform, but that only displays the first record and doesn't update when you look at a different record.

Any ideas?
Thanks,
ChrisOfCatford

11. Registered User
Join Date
Sep 2004
Posts
63
Hello again,

Sorted it out, it was a problem with using a variable that was already defined and using hidden fields in the subform to synch the records.

Thanks everyone for all your help.

ChrisOfCatford

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•