Tim-
Ah, in Access-land, that's called "committing spreadsheet." Hope you see
the light.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of timdbui
Sent: Wednesday, March 13, 2013 12:51 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Averaging multiple fields
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
<mailto:MS_Access_Professionals%40yahoogroups.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
<mailto:MS_Access_Professionals%40yahoogroups.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
<mailto:MS_Access_Professionals%40yahoogroups.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]
>
[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 (7) |
Tidak ada komentar:
Posting Komentar