Results 1 to 7 of 7
  1. #1
    Polarbilly is offline Novice
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Posts
    13

    Averages with zero in calculation

    I have a database which records weights and I want to calculate the Average on each record. My problem is that sometimes not all the fields will be populated . e.g.

    Weight 1 - 35gms
    Weight 2 - 45gms
    Weight 3 - 40gms
    Weight 4 - 0
    Weight 5 - 0
    Average this would normally divide the sum of the Fields by 5 but as only 3 records have data I need to divide by 3, or by however many have been populated...


    Proving a lot harder than anticipated.
    Grateful for any help

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You could restrict any counts and averaging to those records with non-zero values.
    A little more info about the database, tables and purpose would be helpful in getting more focused responses.

  3. #3
    Polarbilly is offline Novice
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Posts
    13
    Hi, thanks for the response. The db records data from fabric which is weighed before testing. Whilst it might be weighed up to 5 times, depending on the tests requested, it may only be weighed 3 or 4 times - hence the potential for blank fields. Hope this helps

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Count the number of non zero values and store that value
    Use the value in your average calculation

    EDIT oops I've just read orange's earlier comment saying much the same thing
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by Polarbilly View Post
    Hi, thanks for the response. The db records data from fabric which is weighed before testing. Whilst it might be weighed up to 5 times, depending on the tests requested, it may only be weighed 3 or 4 times - hence the potential for blank fields. Hope this helps
    You could break your table up into two tables. One main table that stores the information about the fabric, and another table linked to it that just has a the weight recordings. You could have as many or as few recordings as you require. Then you just do a Avg aggregate query on the weights table grouped by each fabric entry.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Why would you have a record for a "non weighing"?
    Your tables should be normalized, and related (at least for this thread's purpose)

    tblFabric--->tblFabricWeighings

  7. #7
    Polarbilly is offline Novice
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Posts
    13
    thanks for all your input
    Best Regards

    Polarbilly

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Calculating Averages
    By chr1stoper1 in forum Access
    Replies: 1
    Last Post: 05-05-2017, 05:26 AM
  2. Replies: 9
    Last Post: 10-30-2015, 05:14 PM
  3. average of averages?
    By mejia.j88 in forum Reports
    Replies: 5
    Last Post: 01-24-2012, 08:09 AM
  4. averages in a report?
    By mejia.j88 in forum Reports
    Replies: 3
    Last Post: 01-18-2012, 06:12 PM
  5. Averages
    By Nixx1401 in forum Queries
    Replies: 1
    Last Post: 05-26-2011, 10:08 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums