Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2004
    Posts
    63

    Question 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. #2
    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]
    Ryan
    My Blog

  3. #3
    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. #4
    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. #5
    Join Date
    Sep 2004
    Posts
    63

    Question Getting there...

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

  6. #6
    Join Date
    Sep 2004
    Posts
    63

    Question 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.

    Here is the link:
    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. #7
    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. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Sep 2004
    Posts
    63

    Talking

    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. #10
    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. #11
    Join Date
    Sep 2004
    Posts
    63

    Smile

    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
  •