averaging in queries

[mattsuppok]

I have three fields in a query and I want to add those three fields and create a column with the average of those totals. Some of the records will be null. What do I do? Simply creating an expression, "avg([field1]+[field2])" doesn't seem to work.. Please advise

 

[Golom]

Try

   ... Avg(Nz([Field1]) + Nz([Field2]) + Nz([Field3])) As AvgValue, ...

 

[mattsuppok]

Don't know if that will work because when I try it I get the same error everytime I try "avg".. the error reads as "you have tried to execute a query that does not include the specified expression 'instrutorlname' as part of an aggregate funciton"... the field 'inst.." is just a field that is on the query. The error on shows when I use a function. I have even tried "sum" and have the same results.... more ideas? thanks

 

[Golom]

OK
If your query is something like this

Select instrutorlname, ... other fields ..., Avg(...)

Then you need a Group By clause for each field that is not part of the Aggregate function (Avg in this case).

   GROUP BY  instrutorlname, ... other fields ...

 

[mattsuppok]

don't want to group by any field.. just want this..

eval1 eval2 eval3 average
2 2 2
3 3 4
3 4

all I want to do is display the average for those 3 fields.

 

[Golom]

OK ... then don't put any other fields in your SELECT statement. The message you're getting is saying that you are selecting fields (specifically instrutorlname) in addition to the field that contains the computed average.