Changing average Query?!

[joebox888]

hi..
I want a Query to calculate the average of all the text boxes that have non-zero values in them. I have 16 text boxes that could potentially all be filled however i would like to be able to calculate only the average of what has been inputted.
Im sure its fairly simple i just cant think of it.. I hope you know what im asking.. The calculation might be 8 values one time and 16 the next.
My textboxes are named x1...x16.
This is roughly what i reckon it should be however its a mess and doesnt work....

SELECT x1, x2, x3, x4, x5, x6, x7, x8, x9, x10, x11, x12, x13, x14, x15, x16,
( x1 + x2 + x3 + x4 + x5 + x6 + x7 + x8 + x9 + x10 +x11 + x12 + x13 + x14 + x15 + x16) As Total,
IIf (x1=0,0,1)+IIf (x2=0,0,1)+IIf(x3=0,0,1)+(x4=0,0,1)+(x5=0,0,1)+(x6=0,0,1)+(x7=0,0,1)+(x8=0,0,1)+(x9=0,0,1)+(x10=0,0,1)+(x11=0,0,1)+(x12=0,0,1)+(x13=0,0,1)+(x14=0,0,1)+(x15=0,0,1)+(x16=0,0,1) As Avg,
IIF (Avg=0,0,Total/Avg) as Average)
FROM LAC;

 

[MajP]

Joe,
I shown you this at least once, how to do this properly. Not sure why you refuse to do it the proper way. That query is getting ridiculously complicated. So one more time you normalize the data, using a union query.

Select
SomeSampleID,
"X1" as DataLabel,
X1 as DataValue
FROM
LAC
UNION
Select
SomeSampleID,
"X2" as DataLabel,
X2 as DataValue
FROM
LAC
....
Select
SomeSampleID,
"X16" as DataLabel,
X16 as DataValue
FROM
LAC


Now using the above query you can simply get the
avg, range, min, max, std for each sample as has already been shown by grouping on the sampleID.

 

[MajP]

see
thread700-1650622
and my reply of 13 Jun 11 11:21
I even demonstrated this with notional data.

I also mentioned in that post if you normalized the data everything after that would be easy, and you would not be stuck with these unnecessarily complicated work-arounds.