Tim-
You are trying to paddle upstream without a paddle!
Neither the number of students nor the number of tests is relevant. Your table for grades should look like:
StudentID
ExamID
Grade
One row per student per exam. To find the average score per student, do:
Select StudentID, Avg(Grade)
From tblGrades
Group By StudentID
John Viescas
Access MVP
Sent from my iPad
On Mar 12, 2013, at 21:01, "timdbui" <timdbui@gmail.com> wrote:
Hi John,
Thanks for your answer, but I need to keep the grades as the fields because the maximum number of tests would be only be 6 while the number of students could be up to 100 or more.
Is there a way for me to get the average with the maximum number of tests set as 6? So for the 4-test takers, the denominator would be 4, and 3 for 3-test takers.
Thanks, John!
--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> 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]
>
[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 (4) |
Tidak ada komentar:
Posting Komentar