Average of multiple fields on one row

[Lockrin]

Hello, I have 5 fields in one row that I want to average. Basically, If all 5 fields would always be filled in I could simply make a calculated field in my query that would look like this

fieldAVG: ([Field1]+[Field2]+[Field3]+[Field4]+[Field5])/5

However, some of these fields will be blank sometimes. When I attempt to average them together I get an average of those fields from every record in the recordset.

[pbaldy]

A shot in the dark:

fieldAVG: ([Field1]+[Field2]+[Field3]+[Field4]+[Field5])/(IIf(IsNull(Field1), 0, 1) + IIf(IsNull(Field2), 0, 1) + ...

Or whatever properly tests your fields, if they aren't Null. The potential for empty fields implies a possible design problem. What kind of data is in the fields?

[Lockrin]

Basically, I am making a spreadsheet-like report that will be sent out automatically. Since it is similar to a spreadsheet and It is weekly information, I have fields:
MField1 TUfield1 Wfield1 THfield1 and Ffield1
Mfield2 TUfield2 Wfield2 THfield2 and Ffield2
Mfield3 TUfield3 Wfield3 THfield3 and Ffield3
Mfield4 TUfield4 Wfield4 THfield4 and Ffield4

I then have calculated fields for That sum these up at the bottom and I want calculated fields that average them on the right side. I could not come up with a way to "normalize" it so that it would still give me the information the way I need it to look. Any ideas?

[TheShabz]

Here's a semi-blind attempt at normalization:
Field1, Field2, Field3, Field4, DayInd, Weekof

Field1-4 = whatever those fields are
DayInd = M,T,W,Th,F
WeekOf = the date of the monday of that week.
if you need a key, you can create a compound key with WeekOf and DayInd

your query would looks something like:
SELECT Field1, Field2, Field3, Field4, DayInd
FROM myTable
WHERE WeekOf = DateFromForm
with that, you can create a form using the DayInd to tell which one is which. Hope this helps