Results 1 to 3 of 3
  1. #1
    karen9975 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2016
    Posts
    1

    expression help

    My database is a QA survey database. Each record is per product. There are 8 fields in my table that are scored 1-5. I need an expression that will average the 8 fields, leaving out blank fields that may not be relevant to each product.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you will need 8 queries put toether into 1 UNION query
    each query will sum the 1 column excluding the nulls.

    Q1:
    select product, count(Product), sum(field1) where [field1] is not null
    union
    select product, count(Product), sum(field2) where [field2] is not null
    union
    select product, count(Product), sum(field3) where [field3] is not null

    then in Q2 you can avg on Q1

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    you could also do

    (iif([field1] between 1 and 5, [field1], 0) + iif([field2] between 1 and 5, [field2], 0) + ... other 6 fields)/(iif([field1] between 1 and 5, 1, 0) + iif([field2] between 1 and 5, 1, 0) + ... other 6 fields)

    It can turn into an ugly formula but it's another option.

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

Similar Threads

  1. Replies: 2
    Last Post: 05-12-2016, 04:31 PM
  2. Replies: 4
    Last Post: 05-03-2016, 01:51 PM
  3. Replies: 5
    Last Post: 09-25-2013, 09:35 AM
  4. Replies: 2
    Last Post: 11-20-2012, 03:21 AM
  5. Replies: 4
    Last Post: 10-26-2012, 12:49 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