Hi! I am having difficulty in coming up with a way to average groups of
values on a current record. I am using Access '97.

I have developed a database application that charts results from a
questionnaire. Each questionnaire has 112 questions to which there is an
answer (0,1,2,3,4 or X). The answers are entered into a form creating a
record. The table format as a textfile would look like this (with the
customer number at the beginning):

STONG01,1,0,X,2,4,4,3,2,4,1,1,0, etc...(113 fields)
JONES04,0,1,2,3,4,2,1,4,X,4,3,0, etc...

What I need to do is (using only the current record showing in the form)
to average the first 7 answer values (fields) and put that result either
in a variable or on another form. Then the next 7 answer values must be
averaged and the result put in a different variable. I would end up with
six{*filter*} unique variables with resulting averages in them. How do I do
this in a function considering I had to use text fields in the table
because some of the answer values are text ("X"). Also, the values need
to be taken from the current record in the table because the answer
values aren't on the form (The averaged results end up being used in an
embedded chart on the same form). As well, only values not "X" can be
averaged so I might average 6 or 5 values.

I would think the function would have to loop through each group of 7
answer values, using the variant datatype, count the valid answer values
within each group of 7 answers, sum the valid answers and divide by the
count. Or can the Access Avg function be used?

Am I on the right track? I have tried queries with limited success. The
Avg function in MS Excel worked fine because of the "general" data
format, but I can't use that as a datatype in a table.

Help would be appreciated! Thanx!