Thanks so much for clearing things up, John.
My problem is I thinking like an Excel user rather than an Access user.
Regards,
Tim
--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> 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@...> 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 (5) |
Tidak ada komentar:
Posting Komentar