Tim-
It looks like your table is badly designed. You should hsve a table with a separate row for each test grade. Then it would be easy to use a Totals query and the Avg function.
When you have a repeating group in a table, if you have a fifth or sixth test, you must change your table design! If you dedign it as a separate table eith one row per test grade, all you have to do is add rows for more tests.
John Viescas
Access MVP
-----Original Message-----
From: timdbui
Sent: 12 Mar 2013 18:01:45 GMT
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Averaging multiple fields
Hi, would some of you please help me with this calculation:
I keep records of test grades for a group of students. Says there are 4 tests total. To calculate the average score for each student, I can easily add up the total 4 score and divide by 4.
However, some students only take 2 tests or 3 tests and I still want to calculate the average of either 2 or 3 tests that those student take.
For the 3-test takers, I need the sum to be divided by 3. For the 2-test takers, I need the sum to be divided by 2.
Is there a formula that I can put in the query to calculate this average? The problem I encounter is for those 2-test or 3-test takers, there is either 1 or 2 null fields, which messes up my calculation.
I had tried the (NZ(Field1)+NZ(Field2)+NZ(Field3) but that doesn't work.
Thanks in advance for your help!
Tim
[Non-text portions of this message have been removed]
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (2) |
Tidak ada komentar:
Posting Komentar