I am grateful for both of your helps, Tim and John!
I find using Access requires me to think much differently from using Excel, but the process is a lot of fun because it is mentally challenging. I am glad to have generous people like all of you to ask questions.
Regards,
Tim B
--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Tim-
>
>
>
> That's excellent advice, Tim #2! I should have thought of suggesting that.
>
>
>
> 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 juiceplustim
> Sent: Wednesday, March 13, 2013 4:56 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: Averaging multiple fields
>
>
>
>
>
> Tim,
> Just to help a bit with converting, you could run the following query 6
> times with a slight modification each time to convert your existing grade
> table into the format being recommended here.
>
> INSERT INTO tblGrades ( StudentID, ExamID, Grade )
> SELECT tblOldGrades.StudentID, 1 AS ExamID, tblOldGrades.Exam1Score
> FROM tblOldGrades;
>
> Change "1 AS ExamID" to "2 AS ExamID" and so on each time you run it and
> change tblOldGrades.Exam1Score to tblOldGrades.Exam2Score and so on as well
> each time (substituting your tables names for these). That will append the
> records of each of the fields in the repeating group to a row of its own in
> the new table with the correct design.
>
> Otherwise, not recommended, you could add a "Exam Average" field and be sure
> the fields where they didn't take a particular test are null and not zero
> (0), and assuming it is 100% possible on each. Then run the following update
> query as a brute force method each time you want an overall test average to
> be recalculated for all students:
>
> UPDATE tblOldGrades SET tblOldGrades.ExamAverage =
> (nz([tblOldGrades]![Exam1Score])+nz([tblOldGrades]![Exam2Score])+nz([tblOldG
> rades]![Exam3Score])+nz([tblOldGrades]![Exam4Score])+nz([tblOldGrades]![Exam
> 5Score])+nz([tblOldGrades]![Exam6Score]))/((IIf(IsNull([tblOldGrades]![Exam1
> Score]),0,1)+IIf(IsNull([tblOldGrades]![Exam2Score]),0,1)+IIf(IsNull([tblOld
> Grades]![Exam3Score]),0,1)+IIf(IsNull([tblOldGrades]![Exam4Score]),0,1)+IIf(
> IsNull([tblOldGrades]![Exam5Score]),0,1)+IIf(IsNull([tblOldGrades]![Exam6Sco
> re]),0,1))*100);
>
> Hope that helps,
> Tim Ritter
> Fort Wayne, IN
>
> --- 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 (10) |
Tidak ada komentar:
Posting Komentar