Average how-to question

[JR]

Each student is rated by several departments as Positive, Neutral, or
Negative. The rating can be selected in an Option Group. When we set up the
Option Group we assigned each option with a numerical value: 3, 2, 1. We want
to average the ratings for each student - how do we do this?
Thanks!

 

[Arvin Meyer MVP]

In a query, click on the Totals (Sigma) button. Change the Group By Total to
Avg.

 

[JR]

I think I didn't give enough/correct info. In the form each student has a
'page' with ratings and comments from 7 staff members so there are 7 Option
Groups. We need to average these seven ratings and that average to be on the
bottom of each student's page. I hope this claifies what we need.
JR

 

[John W. Vinson]

I think I didn't give enough/correct info. In the form each student has a
'page' with ratings and comments from 7 staff members so there are 7 Option
Groups. We need to average these seven ratings and that average to be on the
bottom of each student's page. I hope this claifies what we need.
JR

Steve's suggestion is right on the money. You need to group by Student and by
Page. Since you have not posted any details of your table structure it's hard
to give you step by step instructions; if you'ld like such instructions please
post enough description of the table that someone who cannot see your screen
might be able to help.

 

[JR]

Thanks, OK. The table has the following columns:
StudentId, LastName, FirstName, DormNumber, RARating, TradeName,
TradeRating, CounselorName, CounselorRating, NumeracyRating, GGINumber,
GGIRating, etc., for a total of seven areas to be rated. On the Form, each
Rating is an Option Group with selections Positive, Neutral or Negative. When
we set up the
Option Group we assigned each option with a numerical value: 3, 2, 1,
respectively. At the bottom of each student's form page we want to have an
average of the seven ratings. Student's privileges will be tied to the
average rating.

Hope this helps.
JR

 

[John W. Vinson]

Thanks, OK. The table has the following columns:
StudentId, LastName, FirstName, DormNumber, RARating, TradeName,
TradeRating, CounselorName, CounselorRating, NumeracyRating, GGINumber,
GGIRating, etc., for a total of seven areas to be rated. On the Form, each
Rating is an Option Group with selections Positive, Neutral or Negative. When
we set up the
Option Group we assigned each option with a numerical value: 3, 2, 1,
respectively. At the bottom of each student's form page we want to have an
average of the seven ratings. Student's privileges will be tied to the
average rating.

Hope this helps.

It does; we were all assuming (incorrectly) that your database was properly
normalized, with one *RECORD* per rating rather than one *FIELD* per rating.
You should really consider restructuring your table; not only is it harder to
get an average with this "spreadsheet" design, but if you ever need to add
*another* rating, or change one, or delete one, you'll need to restructure
your table, all your queries, all your forms, and all your reports to
accommodate it. OUCH!

A better design would have a one to many relationship to a Ratinge table with
fields StudentID, RatingType (e.g. "Counselor","Numerancy", "GGI", ... ,
"Database Design", ...) and a number field for Rating. The Totals query will
then work nicely (even if you add new ratings).

With your current design you must use a calculation either on the Form or in
the Query upon which the form is based. if you can assume that none of the
ratings will ever be null, it could be as simple as

=([CounselorRating] + [NumeracyRating] + [GGIRating] + < etc etc >) / 7

If some ratings may be NULL, or if you want to calculate the average for those
which have been filled in and omit the as-yet blank ones, you'll need a more
complex expression such as:

=([CounselorRating] + [NumeracyRating] + [GGIRating] + < etc etc >) /
(7+IsNull([CounselorRating]) + IsNull([NumeracyRating]) + IsNull([GGIRating])
+ <etc etc>)

IsNull will return -1 if the field is NULL, 0 if it isn't, so the denominator
will be the number o non-NULL values.

 

[JR]

I was using a the first calculation as you gave but I didn't account for
blank or Null fields. I'll fix it with your second calculation suggestion and
see if that works.
Thanks!
JR

Thanks, OK. The table has the following columns:
StudentId, LastName, FirstName, DormNumber, RARating, TradeName,
TradeRating, CounselorName, CounselorRating, NumeracyRating, GGINumber,
GGIRating, etc., for a total of seven areas to be rated. On the Form, each
Rating is an Option Group with selections Positive, Neutral or Negative. When
we set up the
Option Group we assigned each option with a numerical value: 3, 2, 1,
respectively. At the bottom of each student's form page we want to have an
average of the seven ratings. Student's privileges will be tied to the
average rating.

Hope this helps.

It does; we were all assuming (incorrectly) that your database was properly
normalized, with one *RECORD* per rating rather than one *FIELD* per rating.
You should really consider restructuring your table; not only is it harder to
get an average with this "spreadsheet" design, but if you ever need to add
*another* rating, or change one, or delete one, you'll need to restructure
your table, all your queries, all your forms, and all your reports to
accommodate it. OUCH!

A better design would have a one to many relationship to a Ratinge table with
fields StudentID, RatingType (e.g. "Counselor","Numerancy", "GGI", ... ,
"Database Design", ...) and a number field for Rating. The Totals query will
then work nicely (even if you add new ratings).

With your current design you must use a calculation either on the Form or in
the Query upon which the form is based. if you can assume that none of the
ratings will ever be null, it could be as simple as

=([CounselorRating] + [NumeracyRating] + [GGIRating] + < etc etc >) / 7

If some ratings may be NULL, or if you want to calculate the average for those
which have been filled in and omit the as-yet blank ones, you'll need a more
complex expression such as:

=([CounselorRating] + [NumeracyRating] + [GGIRating] + < etc etc >) /
(7+IsNull([CounselorRating]) + IsNull([NumeracyRating]) + IsNull([GGIRating])
+ <etc etc>)

IsNull will return -1 if the field is NULL, 0 if it isn't, so the denominator
will be the number o non-NULL values.

 

[John W. Vinson]

I was using a the first calculation as you gave but I didn't account for
blank or Null fields. I'll fix it with your second calculation suggestion and
see if that works.

It certainly would have helped had you posted the table structure and the
nature of the problem you were having at the outset. Hope it works out for
you!