use of Average function

  • Thread starter Thread starter Richard Rosell
  • Start date Start date
R

Richard Rosell

I am a teacher and wish to compute student exam averages in a form or
report. I have sought to use the "avg" function in Access as I would the
"average" function in excel, but find it does not work in a comparable way.
Is there a way to use the "avg" function in a form or report to compute each
students grades or must I sum the grades and divide by the number of exams?

Thanks for any help you might offer.

Richard
 
If your exam scores table is properly normalized, then Avg() will work
great. I would expect to see each grade/score in a record rather than
separate fields in a single record.
 
Hi Richard

To use the Access Avg function, you would apply the following formula to the
Control Source of an unbound Text Box:

=AVG(Expr)

where Expr is the name of the field you wish to average.


Hope this helps


Best Regards

Maurice St-Cyr
Micro Systems Consultants, Inc.
 
Thanks for your help. Let me ask a number of questions:

1. What do you mean when you say the scores table is "properly normalized"?

2. I addition, I am not sure what you mean when you say "I would expect to
see each grade/score in a record rather than
separate fields in a single record. The scores are listed on the table as I
have them in the exam below. Is there a problem with that?

3. I used the formula =Avg(expr) yet it does not compute properly. I know I
am missing a part of the puzzle. Let me show you how I format the
statement:
=Avg([exam 1]+[exam 2]+[exam 3])

Yet, the outcome is never the average of the each student's grades.
Generally, the result I get is much higher than 100.

The table below is a facsimile of what I have.

Soc_Test ID Last First Exam 1 Exam 2 Exam 3
1 MMMMMM LUCY 89 89 88

2 NNNNNNN JOSE 77 80 68
3 OOOOOO ZAK 81 77
97
3 PPPPP RIC 77 90 72


Thanks very much for your assistance

Richard
 
(I am resending this posting as the data in the first one did not format properly. Hope this works better.)Thanks for your help. Let me ask a number of questions:1. What do you mean when you say the scores table is "properly normalized"?


2. I addition, I am not sure what you mean when you say "I would expect to
see each grade/score in a record rather than separate fields in a single record. The scores are listed on the table as I
have them in the exam below. Is there a problem with that?

3. I used the formula =Avg(expr) as you suggested,yet it does not compute properly.
I know I am missing a part of the puzzle. Let me show you how I format the
statement:
=Avg([exam 1]+[exam 2]+[exam 3])

Yet, the outcome is never the average of the each student's grades. Generally, the result I get is much higher than 100.

The table below is a facsimile of the data I use.

ID Last First Exam 1 Exam 2 Exam 3
1 MMMMMM LUCY 89 89 88
2 NNNNNNN JOSE 77 80 68
3 OOOOOO ZAK 81 77 97
4 PPPPP RIC 77 90 72
I have tried to format the data fields in the table as numbers and text. I do not find it makes a difference.

Thanks very much for your assistance.

Richard
 
From the mouth of fellow Access MVP, Jeff Boyce, You are "committing
spreadsheet". There is lots of information on normalization on the web.
There are probably 100s of teachers and instructors each year that ask
similar news group questions and have structures similar to yours.

Getting the average across fields can best be done with and expression like:
=( Nz([Exam1],0) + Nz([Exam2],0) + Nz([Exam3],0) + ... ) / (
IsNull([Exam1]) + 1 + IsNull([Exam2]) + 1 + IsNull([Exam3]) + 1 + ... )
As you can see, this gets fairly nasty as the number of Exams increases.

Normalization is a much better solution.
You need a table of students
tblStudents
===========
StudentID
LastName (don't use "Last" as a field name)
FirstName (don't use "First" as a field name)
....

tblExams
===========
ExamID
ExamDate
ExamPoints
Exam....

tblScores
==========
StudentID link to tblStudents.StudentID
ExamID link to tblExams.ExamID
ExamGrade the number of points
....

Averaging the grades for a student is then a matter of simple SQL.

--
Duane Hookom
MS Access MVP
--

(I am resending this posting as the data in the first one did not format
properly. Hope this works better.)
Thanks for your help. Let me ask a number of questions:
1. What do you mean when you say the scores table is "properly normalized"?


2. I addition, I am not sure what you mean when you say "I would expect to
see each grade/score in a record rather than separate fields in a single
record. The scores are listed on the table as I
have them in the exam below. Is there a problem with that?

3. I used the formula =Avg(expr) as you suggested,yet it does not compute
properly.
I know I am missing a part of the puzzle. Let me show you how I format the
statement:
=Avg([exam 1]+[exam 2]+[exam 3])
Yet, the outcome is never the average of the each student's grades.
Generally, the result I get is much higher than 100.
The table below is a facsimile of the data I use.
ID Last First Exam 1 Exam 2 Exam 3
1 MMMMMM LUCY 89 89 88
2 NNNNNNN JOSE 77 80 68
3 OOOOOO ZAK 81 77 97
4 PPPPP RIC 77 90 72
I have tried to format the data fields in the table as numbers and text. I
do not find it makes a difference.
Thanks very much for your assistance.
Richard
 
Back
Top