Averaging across rows

[AK177]

I have a large dataset within which I need to calculate several averages
across rows rather than within one field (I understand this is not an ideal
design for the data, but that is how they were presented to me). I know there
is a way to do this (using crosstab queries?) but I can't figure out the
proper SQL.

Thanks,
Adrienne

 

[vanderghast]

There is no 'proper' SQL for that. It is like hammering a nail, in a wall,
using a pipe: doable, but just the wrong tool. The tool, database, is built
with, in mind, cases where the data is presented vertically, not
horizontally. In fact, the tool has, in mind, the data presented to it
accordingly to some 'rules' called 'normalization'.


Now, the pipe used to hammer a nail in the wall solution like is :

( Nz(field1, 0) + Nz(field2, 0) + ... + Nz(fieldN, 0) ) / ( iif(field1 IS
null, 0, 1) + iif(field2 IS null, 0, 1) + ... + iif(fieldN IS NULL, 0, 1) )


and even that, it assumes that at least one of involved field is not null
(else a division by zero occurs).



Maybe it would be preferable to 'normalize' your data. Much, Much, MUCH
easier, and performant, even if it is, first time we use it, maybe less
intuitive.




Vanderghast, Access MVP