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([tblOldGrades]![Exam3Score])+nz([tblOldGrades]![Exam4Score])+nz([tblOldGrades]![Exam5Score])+nz([tblOldGrades]![Exam6Score]))/((IIf(IsNull([tblOldGrades]![Exam1Score]),0,1)+IIf(IsNull([tblOldGrades]![Exam2Score]),0,1)+IIf(IsNull([tblOldGrades]![Exam3Score]),0,1)+IIf(IsNull([tblOldGrades]![Exam4Score]),0,1)+IIf(IsNull([tblOldGrades]![Exam5Score]),0,1)+IIf(IsNull([tblOldGrades]![Exam6Score]),0,1))*100);
Hope that helps,
Tim Ritter
Fort Wayne, IN
--- 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 (6) |
Tidak ada komentar:
Posting Komentar