Query Help Please

[jonnymenthol]

Hello,

I have a query which i'm having a problem with.

I have ID, Name and 10 numeric fields.
I then want to have a 14th field which is the average of the 10 numeric fields.

Is this possible?
If so, how?

I've tried Average: AVG(field1+field2+field3 and so on) but this doesn't work. It gives me an error about "tried to execute a query that does not include the specified expression as part of an aggregate function"

Thanks.

 

[namliam]

(field1+field2+field3 and so on)/10

 

[jonnymenthol]

Thanks for that.

Taking it a step further - is there anyway I can average only those fields of the 10 that have values in?

So for one line it might have 5 values, yet another might have 7.

 

[namliam]

Not really... Unless you do something like:
(field1+field2+field3 and so on)/(IIF(ISNULL(FIELD1),0,1)+IIF(ISNULL(FIELD2),0,1)+IIF(ISNULL(FIELD3),0,1)+ and so on)

At the base though this is a design flaw causing this problem 10 the same type of numbers (or anything between 1 and 10) whould be stored in a related table where it would be easy to do an average, the different numbers should be stored as rows, not columns.

 

[vbaInet]

Or:

(field1+field2+field3 + ...)/(IIF(Len([field1] & "") = 0, 0, 1) + IIF(Len([field2] & "") = 0, 0, 1) + IIF(Len([field3] & "") = 0, 0, 1) + ...)