Averages... Could you please Help Me?

Hi. Pleased to meet you, John Vinson

I'll copy the expression but for you to understand my problem you have to
read the messages before this one .....:-(

there it goes:

=(Nz([fieldname1],0) + Nz([fieldname2],0) +... Nz
([fieldname21],0))/ABS(IsNull(Field1) + IsNull(Field2)
+ ...)

I just want to get an average of 21 questions in a questionnaire considering
there may be some questionnaires not fully answered (if there is a NULL
value, I want Access to ignore it and make the average of the existing
answers).

Thank you very much for your help

Júlia



"John Vinson" escreveu:

On Mon, 13 Sep 2004 10:49:02 -0700, Júlia
wrote:

“The expression you entered contains invalid syntax. You omitted an operand
or operator, you entered invalid character or comma, or you entered text
without surrounding it in quotation marks�.

Please copy and paste the entire expression to a message in the
newsgroup.

John W. Vinson[MVP]
(no longer chatting for now)

=(Nz([fieldname1],0) + Nz([fieldname2],0) +... Nz
([fieldname21],0))/ABS(IsNull(Field1) + IsNull(Field2)
+ ...)

Julia,
It looks like what you did was simply copy and paste Chris's example into
your query. That won't work. You are going to have to put an
NZ([Fieldname#],0) and an IsNull(Field#) for every field you are wanting to
average. When Chris put in the "..." he was indicating that you would have
to continue on from the previous field to the last field. Thus, if we were
using 4 fields you would have to do this:

=(Nz([Fieldname1],0) + Nz([Fieldname2],0) + Nz([Fieldname3],0) +
Nz([Fieldname4],0)) /
ABS(IsNull([Field1]) + IsNull([Field2]) + IsNull([Field3]) +
IsNull([Field4]))

In your case you will need to go all the way to Field21.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Júlia" wrote in message
...
Hi. Pleased to meet you, John Vinson

I'll copy the expression but for you to understand my problem you have to
read the messages before this one .....:-(

there it goes:

=(Nz([fieldname1],0) + Nz([fieldname2],0) +... Nz
([fieldname21],0))/ABS(IsNull(Field1) + IsNull(Field2)
+ ...)

I just want to get an average of 21 questions in a questionnaire
considering
there may be some questionnaires not fully answered (if there is a NULL
value, I want Access to ignore it and make the average of the existing
answers).

Thank you very much for your help

Júlia



"John Vinson" escreveu:

On Mon, 13 Sep 2004 10:49:02 -0700, Júlia
wrote:

"The expression you entered contains invalid syntax. You omitted an
operand
or operator, you entered invalid character or comma, or you entered
text
without surrounding it in quotation marks".

Please copy and paste the entire expression to a message in the
newsgroup.

John W. Vinson[MVP]
(no longer chatting for now)

On Tue, 14 Sep 2004 05:49:01 -0700, Júlia
wrote:

Hi. Pleased to meet you, John Vinson

I'll copy the expression but for you to understand my problem you have to
read the messages before this one .....:-(

there it goes:

=(Nz([fieldname1],0) + Nz([fieldname2],0) +... Nz
([fieldname21],0))/ABS(IsNull(Field1) + IsNull(Field2)
+ ...)

I just want to get an average of 21 questions in a questionnaire considering
there may be some questionnaires not fully answered (if there is a NULL
value, I want Access to ignore it and make the average of the existing
answers).

Just remember: YOU can see your database. YOU know what your
fieldnames are. Neither Chris nor I have this information (and no,
please do not upload the database). The code above was an *example*
which you need to customize using your own field names.

For instance if you have fields named Cuestion1, Cuestion2 and
Cuestion3, the expression would be

=(NZ([Cuestion1],0) + Nz([Cuestion2],0) + Nz([Cuestion3],
0))/Abs(IsNull([Cuestion1]) + IsNull([Cuestion2]) +
IsNull([Cuestion3]))

If you have 21 fields with other names, you'll need a very long
expression with all 21 fieldnames in the expression before the /
divide operator, and again afterward.


John W. Vinson[MVP]
(no longer chatting for now)

Hi again, Lynn

I know I am a novice using the Access and I now nothing but of course I did
not copy and paste Chris's example into my query....!!! I already tried many
ways and read a litle before I asked help in this site, which I didn't know,
by the way.

Anyway, I managed to get averages for each field across the questionnaires
and even to see the average of each questionnaire in the form, as I'm filling
it (that was by creating a simple formula, too).

I know I'm making some mistake but I tried lots of times to apply Chris's
formula and I always get the message saying there is an error.

Could you please tell me if, in Chris's formula, Fieldname1 and Field1 mean
the same field?

Even though, I'll try one more time......

Thanks a lot for your help.

Júlia



"Lynn Trapp" escreveu:

=(Nz([fieldname1],0) + Nz([fieldname2],0) +... Nz
([fieldname21],0))/ABS(IsNull(Field1) + IsNull(Field2)
+ ...)

Julia,
It looks like what you did was simply copy and paste Chris's example into
your query. That won't work. You are going to have to put an
NZ([Fieldname#],0) and an IsNull(Field#) for every field you are wanting to
average. When Chris put in the "..." he was indicating that you would have
to continue on from the previous field to the last field. Thus, if we were
using 4 fields you would have to do this:

=(Nz([Fieldname1],0) + Nz([Fieldname2],0) + Nz([Fieldname3],0) +
Nz([Fieldname4],0)) /
ABS(IsNull([Field1]) + IsNull([Field2]) + IsNull([Field3]) +
IsNull([Field4]))

In your case you will need to go all the way to Field21.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Júlia" wrote in message
...
Hi. Pleased to meet you, John Vinson

I'll copy the expression but for you to understand my problem you have to
read the messages before this one .....:-(

there it goes:

=(Nz([fieldname1],0) + Nz([fieldname2],0) +... Nz
([fieldname21],0))/ABS(IsNull(Field1) + IsNull(Field2)
+ ...)

I just want to get an average of 21 questions in a questionnaire
considering
there may be some questionnaires not fully answered (if there is a NULL
value, I want Access to ignore it and make the average of the existing
answers).

Thank you very much for your help

Júlia



"John Vinson" escreveu:

On Mon, 13 Sep 2004 10:49:02 -0700, Júlia
wrote:

"The expression you entered contains invalid syntax. You omitted an
operand
or operator, you entered invalid character or comma, or you entered
text
without surrounding it in quotation marks".

Please copy and paste the entire expression to a message in the
newsgroup.

John W. Vinson[MVP]
(no longer chatting for now)

I know I am a novice using the Access and I now nothing but of course I
did
not copy and paste Chris's example into my query....!!! I already tried
many
ways and read a litle before I asked help in this site, which I didn't
know,
by the way.

Not to worry about being a novice. We were all novices at some point.


Could you please tell me if, in Chris's formula, Fieldname1 and Field1
mean
the same field?

YES, I'm quite sure they are.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Júlia" wrote in message
...
Hi again, Lynn

I know I am a novice using the Access and I now nothing but of course I
did
not copy and paste Chris's example into my query....!!! I already tried
many
ways and read a litle before I asked help in this site, which I didn't
know,
by the way.

Anyway, I managed to get averages for each field across the questionnaires
and even to see the average of each questionnaire in the form, as I'm
filling
it (that was by creating a simple formula, too).

I know I'm making some mistake but I tried lots of times to apply Chris's
formula and I always get the message saying there is an error.

Could you please tell me if, in Chris's formula, Fieldname1 and Field1
mean
the same field?

Even though, I'll try one more time......

Thanks a lot for your help.

Júlia



"Lynn Trapp" escreveu:

=(Nz([fieldname1],0) + Nz([fieldname2],0) +... Nz
([fieldname21],0))/ABS(IsNull(Field1) + IsNull(Field2)
+ ...)

Julia,
It looks like what you did was simply copy and paste Chris's example
into
your query. That won't work. You are going to have to put an
NZ([Fieldname#],0) and an IsNull(Field#) for every field you are wanting
to
average. When Chris put in the "..." he was indicating that you would
have
to continue on from the previous field to the last field. Thus, if we
were
using 4 fields you would have to do this:

=(Nz([Fieldname1],0) + Nz([Fieldname2],0) + Nz([Fieldname3],0) +
Nz([Fieldname4],0)) /
ABS(IsNull([Field1]) + IsNull([Field2]) + IsNull([Field3]) +
IsNull([Field4]))

In your case you will need to go all the way to Field21.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Júlia" wrote in message
...
Hi. Pleased to meet you, John Vinson

I'll copy the expression but for you to understand my problem you have
to
read the messages before this one .....:-(

there it goes:

=(Nz([fieldname1],0) + Nz([fieldname2],0) +... Nz
([fieldname21],0))/ABS(IsNull(Field1) + IsNull(Field2)
+ ...)

I just want to get an average of 21 questions in a questionnaire
considering
there may be some questionnaires not fully answered (if there is a
NULL
value, I want Access to ignore it and make the average of the existing
answers).

Thank you very much for your help

Júlia



"John Vinson" escreveu:

On Mon, 13 Sep 2004 10:49:02 -0700, Júlia
wrote:

"The expression you entered contains invalid syntax. You omitted an
operand
or operator, you entered invalid character or comma, or you entered
text
without surrounding it in quotation marks".

Please copy and paste the entire expression to a message in the
newsgroup.

John W. Vinson[MVP]
(no longer chatting for now)

Hi, John Vinson

I will stop bugging you. I'm sorry for my insistence. I appreciated your
patience and your detailed explanations to me, but there was nothing new
because that was exactly what I've tried to do before, exactly with my
fieldnames in my database. I don't know why it doesn't work (I did not just
copy and paste Chris's example) but I'm sure it is because I'm making some
mistake.

THANKS again ro ALL OF YOU for your help.

Júlia



"John Vinson" escreveu:

On Tue, 14 Sep 2004 05:49:01 -0700, Júlia
wrote:

Hi. Pleased to meet you, John Vinson

I'll copy the expression but for you to understand my problem you have to
read the messages before this one .....:-(

there it goes:

=(Nz([fieldname1],0) + Nz([fieldname2],0) +... Nz
([fieldname21],0))/ABS(IsNull(Field1) + IsNull(Field2)
+ ...)

I just want to get an average of 21 questions in a questionnaire considering
there may be some questionnaires not fully answered (if there is a NULL
value, I want Access to ignore it and make the average of the existing
answers).

Just remember: YOU can see your database. YOU know what your
fieldnames are. Neither Chris nor I have this information (and no,
please do not upload the database). The code above was an *example*
which you need to customize using your own field names.

For instance if you have fields named Cuestion1, Cuestion2 and
Cuestion3, the expression would be

=(NZ([Cuestion1],0) + Nz([Cuestion2],0) + Nz([Cuestion3],
0))/Abs(IsNull([Cuestion1]) + IsNull([Cuestion2]) +
IsNull([Cuestion3]))

If you have 21 fields with other names, you'll need a very long
expression with all 21 fieldnames in the expression before the /
divide operator, and again afterward.


John W. Vinson[MVP]
(no longer chatting for now)

I am working on something similar and I also could not get it to work. After
searching some other posts I was able to get it to work by making a few
changes. I removed the "ABS" after the "/" and added a "10+" (mine only has
10 fields) before the first "isnull". I'm no expert, but it's doing what I
need it to do. Here's what I'm using for my 10 field average:

=(Nz([evaluation_1],0)+Nz([evaluation_2],0)+Nz([evaluation_3],0)+Nz([evaluation_4],0)+Nz([evaluation_5],0)+Nz([evaluation
6],0)+Nz([evaluation_7],0)+Nz([evaluation_8],0)+Nz([evaluation_9],0)+Nz([evaluation_10],0))/(10+IsNull([evaluation_1])+IsNull([evaluation_2])+IsNull([evaluation_3])+IsNull([evaluation_4])+IsNull([evaluation_5])+IsNull([evaluation
6])+IsNull([evaluation_7])+IsNull([evaluation_8])+IsNull([evaluation_9])+IsNull([evaluation_10]))


"Lynn Trapp" wrote:

I know I am a novice using the Access and I now nothing but of course I
did
not copy and paste Chris's example into my query....!!! I already tried
many
ways and read a litle before I asked help in this site, which I didn't
know,
by the way.

Not to worry about being a novice. We were all novices at some point.


Could you please tell me if, in Chris's formula, Fieldname1 and Field1
mean
the same field?

YES, I'm quite sure they are.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Júlia" wrote in message
...
Hi again, Lynn

I know I am a novice using the Access and I now nothing but of course I
did
not copy and paste Chris's example into my query....!!! I already tried
many
ways and read a litle before I asked help in this site, which I didn't
know,
by the way.

Anyway, I managed to get averages for each field across the questionnaires
and even to see the average of each questionnaire in the form, as I'm
filling
it (that was by creating a simple formula, too).

I know I'm making some mistake but I tried lots of times to apply Chris's
formula and I always get the message saying there is an error.

Could you please tell me if, in Chris's formula, Fieldname1 and Field1
mean
the same field?

Even though, I'll try one more time......

Thanks a lot for your help.

Júlia



"Lynn Trapp" escreveu:

=(Nz([fieldname1],0) + Nz([fieldname2],0) +... Nz
([fieldname21],0))/ABS(IsNull(Field1) + IsNull(Field2)
+ ...)

Julia,
It looks like what you did was simply copy and paste Chris's example
into
your query. That won't work. You are going to have to put an
NZ([Fieldname#],0) and an IsNull(Field#) for every field you are wanting
to
average. When Chris put in the "..." he was indicating that you would
have
to continue on from the previous field to the last field. Thus, if we
were
using 4 fields you would have to do this:

=(Nz([Fieldname1],0) + Nz([Fieldname2],0) + Nz([Fieldname3],0) +
Nz([Fieldname4],0)) /
ABS(IsNull([Field1]) + IsNull([Field2]) + IsNull([Field3]) +
IsNull([Field4]))

In your case you will need to go all the way to Field21.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Júlia" wrote in message
...
Hi. Pleased to meet you, John Vinson

I'll copy the expression but for you to understand my problem you have
to
read the messages before this one .....:-(

there it goes:

=(Nz([fieldname1],0) + Nz([fieldname2],0) +... Nz
([fieldname21],0))/ABS(IsNull(Field1) + IsNull(Field2)
+ ...)

I just want to get an average of 21 questions in a questionnaire
considering
there may be some questionnaires not fully answered (if there is a
NULL
value, I want Access to ignore it and make the average of the existing
answers).

Thank you very much for your help

Júlia



"John Vinson" escreveu:

On Mon, 13 Sep 2004 10:49:02 -0700, Júlia
wrote:

"The expression you entered contains invalid syntax. You omitted an
operand
or operator, you entered invalid character or comma, or you entered
text
without surrounding it in quotation marks".

Please copy and paste the entire expression to a message in the
newsgroup.

John W. Vinson[MVP]
(no longer chatting for now)

RedL wrote:

I am working on something similar and I also could not get it to work. After
searching some other posts I was able to get it to work by making a few
changes. I removed the "ABS" after the "/" and added a "10+" (mine only has
10 fields) before the first "isnull". I'm no expert, but it's doing what I
need it to do. Here's what I'm using for my 10 field average:

=(Nz([evaluation_1],0)+Nz([evaluation_2],0)+Nz([evaluation_3],0)+Nz([evaluation_4],0)+Nz([evaluation_5],0)+Nz([evaluation
6],0)+Nz([evaluation_7],0)+Nz([evaluation_8],0)+Nz([evaluation_9],0)+Nz([evaluation_10],0))/(10+IsNull([evaluation_1])+IsNull([evaluation_2])+IsNull([evaluation_3])+IsNull([evaluation_4])+IsNull([evaluation_5])+IsNull([evaluation
6])+IsNull([evaluation_7])+IsNull([evaluation_8])+IsNull([evaluation_9])+IsNull([evaluation_10]))

Your calculation probably works for what you want to do, but your table
structure looks a bit strange...

Isn't the [evaluation_1] field pretty similar to your [evaluation_9]
field? Same type of data, similar meaning, etc.?

My suggestion (not for now, maybe, but for when you need to redo your
table design to add or change fields) would be to set up a separate
Table with just 2 or 3 fields in it, something like this:


[Evaluation] Table:

[EvaluationID] Autonumber -- primary key
[PersonID] Number (Long integer) -- link to table of responders
[Type] Number (Short integer) -- question number
[Value] Number (Single) -- questionnaire answer

The "Autonumber" field you might not need, but some operations require
you to include a "Primary Key" field. The [Value] field would contain
all the numbers that you averaged in your expression, but you could
express it more simply if they're in a table like this. The [Type]
field would identify which question the [Value] field answers (one per
record, not 10). The [PersonID] field would correspond to the record
number you're currently using, and would identify one of the
questionnaires -- but any other information about that questionnaire,
other than the actual answers, would be in a separate [Person] Table,
which could have a primary key called [PersonID] (same name as in
[Evaluation], except that here it's a foreign key, and you can have
several answers linked to one questionnaire).

As I mentioned, if you're happy with what you have now, don't change it,
but your current design looks to me kind of difficult to maintain, so I
suggest you think about redoing it when you have a chance. There are
ways to set up the new Tables without having to re-enter all of the
data; if you're not sure how, I'm sure that people on m.p.a.g. will be
happy to advise you.

-- Vincent Johns
Please feel free to quote anything I say here.