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