Avg Function not working in a query 1

[BrenoAguiar]

HI folks,

I have a Select query like this:

SELECT COSTHIT.Media_cd, COSTHIT.Media_type_name, COSTHIT.CostHit_Jan, COSTHIT.CostHit_Feb, COSTHIT.CostHit_Mar, COSTHIT.CostHit_Apr, COSTHIT.CostHit_May, COSTHIT.CostHit_Jun, COSTHIT.CostHit_Jul, COSTHIT.CostHit_Aug, COSTHIT.CostHit_Sep, COSTHIT.CostHit_Oct, COSTHIT.CostHit_Nov, COSTHIT.CostHit_Dec
FROM COSTHIT;

And I wanted to write a field expression that would Average the "costhit_jan" + "Costhit_feb" + "costhit_Mar" ... for each individual "Media_cd". Of course some of those values would be Null and should not afect the AVG calc.

Any help?

 

[PHV]

First, create a normalisation query named, say, qryCOSTHITnorm:
SELECT Media_cd, Media_type_name, 'Jan' AS [Month], CostHit_Jan AS Cost
FROM COSTHIT WHERE CostHit_Jan Is Not Null
UNION SELECT Media_cd, Media_type_name, 'Feb', CostHit_Feb
FROM COSTHIT WHERE CostHit_Feb Is Not Null
...
UNION SELECT Media_cd, Media_type_name, 'Dec', CostHit_Dec
FROM COSTHIT WHERE CostHit_Dec Is Not Null
;
And now your average query:
SELECT Media_cd, Media_type_name, Avg(Cost) AS CostAverage
FROM qryCOSTHITnorm
GROUP BY Media_cd, Media_type_name;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BrenoAguiar]

Brilliant! thank you Again PHV.